IF NOT EXISTS(SELECT *
                from sys.objects SO
                     inner join sys.indexes si
                             ON si.[object_id] = SO.[object_id]
                            AND SI.[name] = 'DelegationDates'
               WHERE so.[name] = 'DiaryDelegations')
BEGIN
CREATE NONCLUSTERED INDEX 
       [DelegationDates]
    ON [dbo].[DiaryDelegations] 
      ([DATE] ASC,
       [DelType] ASC,
       [AssignNo] ASC)
    ON [PRIMARY]
END
GO

IF NOT EXISTS(SELECT *
                from sys.objects SO
                     inner join sys.indexes si
                             ON si.[object_id] = SO.[object_id]
                            AND SI.[name] = 'DelegationDates2'
               WHERE so.[name] = 'DiaryDelegations')
BEGIN
  CREATE NONCLUSTERED INDEX 
         [DelegationDates2]
      ON [dbo].[DiaryDelegations]
        ([DATE],
         [DelType])
INCLUDE ([ACTIONID],
         [TIME],
         [AssignNo])
  END
GO
/**************************************************************************************
 *
 * DELETE Functions and Procedures that will be re-generated in this script.
 *
 **************************************************************************************/
IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDPushTask3'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDPushTask3]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListCount2'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListCount2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListCountByHighlight2'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListCountByHighlight2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListCountByActionType2'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListCountByActionType2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByHighlightPage2'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListByHighlightPage2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByHighlightPage2Rev'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListByHighlightPage2Rev]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByActionTypePage2'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListByActionTypePage2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByActionTypePage2Rev'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListByActionTypePage2Rev]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListPage'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListPage]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListPage2'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListPage2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListPage2Rev'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDTaskListPage2Rev]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListSet2'
             AND [XTYPE] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[ky_WDTaskListSet2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListSet2Rev'
             AND [XTYPE] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[ky_WDTaskListSet2Rev]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByHighlightSet2'
             AND [XTYPE] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[ky_WDTaskListByHighlightSet2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByHighlightSet2Rev'
             AND [XTYPE] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[ky_WDTaskListByHighlightSet2Rev]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByActionTypeSet2'
             AND [XTYPE] = 'TF')
    Drop function [dbo].[ky_WDTaskListByActionTypeSet2]
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDTaskListByActionTypeSet2Rev'
             AND [XTYPE] = 'TF')
    Drop function [dbo].[ky_WDTaskListByActionTypeSet2Rev]
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDUpdateTimeEntry2'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDUpdateTimeEntry2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDUpdateTimeEntry'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDUpdateTimeEntry]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDSetTimeDayBookRateCharge'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDSetTimeDayBookRateCharge]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDGetTAList2'
             AND [XTYPE] = 'TF')
    Drop function [dbo].[ky_WDGetTAList2]
GO

IF EXISTS (SELECT * 
             from sys.objects SO
            where SO.[name] = 'ky_WDCompleteTranscription'
              and SO.[type] = 'P')
BEGIN
  drop procedure [dbo].[ky_WDCompleteTranscription]
END
GO

IF EXISTS (SELECT * 
             from sys.objects SO
            where SO.[name] = 'ky_WDCreateTranscription'
              and SO.[type] = 'P')
BEGIN
  drop procedure [dbo].[ky_WDCreateTranscription]
END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_WDActionComplete3'
             AND [XTYPE] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[ky_WDActionComplete3]
  END
GO

IF EXISTS(SELECT *
            FROM sys.objects SO
           WHERE SO.[name] = 'ky_WDAllCaseAssociates2'
             AND SO.[type] = 'TF')
  BEGIN
    DROP FUNCTION [dbo].[ky_WDAllCaseAssociates2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_RemoveSpuriousWhitespace2'
             AND [XTYPE] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[ky_RemoveSpuriousWhitespace2]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_getDuration'
             AND [XTYPE] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[ky_getDuration]
  END
GO

IF EXISTS(SELECT *
            FROM SYSOBJECTS
           WHERE [NAME] = 'ky_NewerAssignments'
             AND [XTYPE] = 'FN')
  BEGIN
    DROP FUNCTION [dbo].[ky_NewerAssignments]
  END
GO

/**************************************************************************************
 *
 * (RE)-Create Functions and Procedures that will be re-generated in this script.
 *
 **************************************************************************************/
GO

/**************************************************************************************
 *
 * ky_NewerAssignments
 *
 * Checks whether there are newer Diary Delegation records in the system that fit the selection
 *  criteria. (If there are, this row should be excluded from the results)
 *
 **************************************************************************************/
CREATE FUNCTION [dbo].[ky_NewerAssignments]
               (@AssignNo int,
                @startdate datetime,
                @enddate datetime,
                @handler varchar(10),
                @team varchar(10),
                @outstanding int,
                @ActionType varchar(500))
RETURNS int
AS
BEGIN

DECLARE @total int

SELECT @total = DC.[TOTAL]
  FROM [dbo].[DiaryDelegations] DEL
       CROSS APPLY (SELECT COUNT(1) AS [TOTAL]
                      FROM [dbo].[DiaryDelegations] D2
                     WHERE D2.[ACTIONID] = DEL.[ACTIONID]
                       AND D2.[HANDLER] = CASE WHEN @handler = '' THEN D2.[HANDLER] ELSE @handler END
                       AND D2.[TEAM] = CASE WHEN @team = '' THEN D2.[TEAM] ELSE @team END
                       AND D2.[STATUS] = CASE WHEN @outstanding = 0 THEN D2.[STATUS] ELSE 0 END
                       AND D2.[DATE] >= @startdate
                       AND D2.[DATE] < @enddate
                       AND (   @ActionType LIKE '%*' + D2.[ACTIONTYPE] + '*%'
                             OR ISNULL(@ActionType, '') = '')
                       AND D2.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND D2.[AssignNo] > DEL.[AssignNo]) DC
 WHERE DEL.[AssignNo] = @AssignNo
 
 RETURN @total
 END
 GO
 
create function 
       [dbo].[ky_getDuration]
      (@Start          varchar(10),
       @End            varchar(10),
       @MinDuration    int)
RETURNS int
AS
BEGIN
  DECLARE @iStart int
  DECLARE @iEnd int
  DECLARE @diff int
  
  IF ISNUMERIC(@Start) = 0
    SET @iStart = 0
  ELSE
    SET @iStart = CONVERT(int, @Start)

  IF ISNUMERIC(@End) = 0
    SET @iEnd = 0
  ELSE
    SET @iEnd = CONVERT(int, @End)

  SET @diff = @iEnd - @iStart
  IF (@diff < (6000 * @MinDuration))
    RETURN @MinDuration

  SET @diff = CONVERT(int, (@diff / 6000))
      
  RETURN @diff
END
GO

/**************************************************************************************
 *
 * ky_RemoveSpuriousWhitespace2
 *
 * Removes newlines, line feeds, non-breaking spaces and multiple spaces from the input
 *   string in order to produce an output format suitable for display in lists.
 *
 **************************************************************************************/
create function
       [dbo].[ky_RemoveSpuriousWhitespace2]
      (@original varchar(5000),
       @maxlen   int)
returns varchar(5000)
AS
BEGIN
  DECLARE @len int

  SET @original = replace(@original, char(10), ' ')
  SET @original = replace(@original, char(13), ' ')
  SET @original = replace(@original, char(160), ' ')

  SET @original = RTRIM(@original)
  
  SET @len = 0
    WHILE (@len <> len(@original))
      BEGIN
        SET @len = len(@original)
        SET @original = replace(@original, '  ', ' ')
      END

  RETURN substring(@original, 1, @maxlen)
  
END

GO

/**************************************************************************************
 *
 * ky_WDActionComplete3
 *
 * Completes / Returns an Action - depending on whether this SP is invoked by the owner
 *   or a delegate. Also allows for taking ownership
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDActionComplete3]
      (@ActionID int,
       @CurFee varchar(10),
       @CurTeam varchar(10),
       @NextActionsXML varchar(4000),
       @TakeOwnership int)
AS
BEGIN

DECLARE @Now datetime
DECLARE @NowDateOnly datetime
DECLARE @NowClarionTime char(10)
DECLARE @NowClarionEndTime char(10)
DECLARE @Continue int

DECLARE @Owner int

DECLARE @IsAuthorisedToComplete int
DECLARE @CountFailedDependentActions int
DECLARE @idoc int

DECLARE @VCActionID varchar(50)
DECLARE @MaxActionID int
DECLARE @LocDel      varchar(10)
DECLARE @WorkProcess int

DECLARE @NA TABLE
       ([ID]          int identity(1, 1),
        [ActionID]    int,
        [Code]        varchar(5))

DECLARE @AlarmType int
DECLARE @DisabledAlarmStatusID int
DECLARE @ActionTypeID int

SELECT @AlarmType = ISNULL(ALT1.[TypeID], ALT2.[TypeID])
  FROM AlarmTypes ALT2
       LEFT OUTER JOIN AlarmTypes ALT1
                    ON ALT1.[Description] = 'Action'
 WHERE ALT2.[Description] = 'Generic Alarm'

Select @DisabledAlarmStatusID = IsNull(ALS2.[StatusID], ALS1.[StatusID])
  from [dbo].[AlarmStatus] ALS1
       LEFT OUTER JOIN [dbo].[AlarmStatus] ALS2
                    ON ALS2.[Description] = convert(nvarchar(500), 'Actioned')
 WHERE ALS1.[Description] = N'Disabled'

Select @ActionTypeID = ALT1.[TypeID]
  from [dbo].[AlarmTypes] ALT1
 WHERE ALT1.[Description] = N'Action'

IF (RTRIM(ISNULL(@CurTeam,'')) = '')
  BEGIN
    Select @CurTeam = HAN.[TEAM]
      from [dbo].[Handlers] HAN
     WHERE HAN.[CODE] = @CurFee
  END
  
/* If @Continue is set to a value other than zero at any point during this process,
   stop processing further data, and make sure that the transaction at the end is rolled back */
SET @Continue = 0

/* Some processes are dependent on the current fee handler being the owner of the action
   that is to be completed, others require that the action is/isn't a phone call or e-mail */
SELECT @Owner = CASE DIA.[FNCODE] 
                     WHEN @CurFee
                          THEN 1
                     ELSE 0 END,
       @WorkProcess = ISNULL(DIA.[WORKPROCESS], 0)
  FROM [dbo].[Diary] DIA
 WHERE DIA.[ACTIONID] = @ActionID

IF (@TakeOwnership = 1)
  SET @Owner = 1

/* Check that this fee earner is authorised to complete this action. Note that this should
   also be checked by the client application, before calling this procedure, but we should
   still check it here in case this somehow gets invoked while bypassing the client checks. */ 
SELECT @IsAuthorisedToComplete = [IsAuthorisedToComplete]
  FROM [dbo].[ky_WDActionAuthorisedToComplete](@ActionID, @CurFee)

/* if not authorised, do not do anything else */
SET @Continue = @IsAuthorisedToComplete 

IF (@Continue = 0)
  BEGIN
  
    /* this function returns error messages for each dependent action that has not been
       completed within the required timeframe. This should also be checked in the client
       application, and it should display the error messages returned by this function. Here
       it suffices to make sure there are 0 messages before we proceed. */
    SELECT @CountFailedDependentActions = COUNT(1)
      FROM [dbo].[ky_WDActionCheckDependentActions](@ActionID)
  
    /* If there are any messages, do not continue */
    SET @Continue = @CountFailedDependentActions 
  END
-- END IF (@Continue = 0)

/* now all the checks are done, we are going to make actual database changes. All this should
   be atomic so we wrap the whole lot into a transaction */
BEGIN TRANSACTION

BEGIN TRY
  BEGIN
    IF (@Continue = 0)
      BEGIN

        /* some date and time variables that may be used in several of the updates that follow */
          -- full date/time
        SET @Now = GETDATE()

          -- date only, time set to 00:00:00
        SET @NowDateOnly = convert(datetime, convert(varchar, @Now, 112))

          -- Clarion compliant representation of the Time part of the date/time
        SET @NowClarionTime = convert(char(10), [dbo].[ky_ConvertTimeToClarion](@Now))
        SET @NowClarionEndTime = convert(char(10), [dbo].[ky_ConvertTimeToClarion](dateadd(mi, 30, @Now)))

        SELECT @LocDel = DEL.[DELEGATE]
          FROM [dbo].[DiaryDelegations] DEL
         WHERE DEL.[ACTIONID] = @ActionID
           AND DEL.[HANDLER] = @CurFee
           AND ISNULL(DEL.[STATUS], 0) = 0

        IF (@@ERROR <> 0)
          BEGIN
            /* this will eventually lead to a rollback */
            SET @Continue = 1
          END
        -- END IF (@@ERROR <> 0)

        /* update all Diary delegation records that were assigned to the current handler */
        UPDATE DEL
           SET DEL.[DATER] = @NowDateOnly, 
               DEL.[TIMER] = @NowClarionTime,
               DEL.[STATUS] = 1
          FROM [dbo].[DiaryDelegations] DEL
         WHERE DEL.[ACTIONID] = @ActionID
           AND DEL.[HANDLER] = @CurFee
           AND IsNull(DEL.[Status], 0) = 0

        IF (@@ERROR <> 0)
          BEGIN
            /* this will eventually lead to a rollback */
            SET @Continue = 1
          END
        -- END IF (@@ERROR <> 0)

        IF (@Owner = 1)  
          BEGIN

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */

                /* Phone and E-mail records */
                UPDATE DIA
                   SET DIA.[STATUS] = 1,
                       DIA.[PROCESSSTATUS] = 0,
                       DIA.[FNCODE] = CASE WHEN @TakeOwnership = 1 THEN @CurFee ELSE DIA.[FNCODE] END,
                       DIA.[TEAMCODE] = CASE WHEN @TakeOwnership = 1 THEN @CurTeam ELSE DIA.[TEAMCODE] END
                  FROM [dbo].[Diary] DIA
                 WHERE DIA.[ACTIONID] = @ActionID
                   AND ISNULL(DIA.[ACTIONTYPE],'') <> 'A' -- IN ('T', 'E', 'N', 'P')

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0)

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */

                /* If it is a normal Action, update some date information, too */
                UPDATE DIA
                   SET DIA.[STATUS] = 1,
                       DIA.[PROCESSSTATUS] = 0,
                       DIA.[DATE] = @Now,
                       DIA.[DYSTARTTIME] = @NowClarionTime,
                       DIA.[DYENDTIME] = @NowClarionEndTime,
                       DIA.[FNCODE] = CASE WHEN @TakeOwnership = 1 THEN @CurFee ELSE DIA.[FNCODE] END,
                       DIA.[TEAMCODE] = CASE WHEN @TakeOwnership = 1 THEN @CurTeam ELSE DIA.[TEAMCODE] END
                  FROM [dbo].[Diary] DIA
                 WHERE DIA.[ACTIONID] = @ActionID
                   AND IsNull(DIA.[ACTIONTYPE], '') = 'A' --NOT IN ('T', 'E', 'N', 'P')

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0)

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */

                SET @VCActionID = convert(varchar(50), @ActionID)

                /* Better to keep these updates in-line since we're wrapping it all 
                   into a transaction;
                   we don't want to introduce further complications with calls to
                   external procedures that update the database at this point.

                  Originally:
                  EXEC [dbo].[ky_SetLinkedAlarms] 'Actioned', 'Action', @VCActionID */

                UPDATE ALM
                   SET ALM.[Status] = @DisabledAlarmStatusID
                  FROM [dbo].[Alarms] ALM
                 WHERE ALM.[Type] = @ActionTypeID
                   AND ALM.[LinkID] = @VCActionID

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0) 

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */

                /* When applicable, make sure that the Matter status reflects the status
                   of this completed action */
                UPDATE MAT
                   SET MAT.[Status] = TAC.[STATUS]  
                  FROM [dbo].[Diary] DIA
                       INNER JOIN [dbo].[matters] MAT
                               ON MAT.[Code] = DIA.[CASECODE]
                       INNER JOIN [dbo].[TemplateActions] TAC
                               ON TAC.[ACTIONCODE] = DIA.[ACTIONCODE]
                              AND ISNULL(TAC.[STATUS], '') NOT IN ('NA', 'N/A', '') 
                 WHERE DIA.[ACTIONID] = @ActionID

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0) 

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */

                /* Update Case Master */
                UPDATE CSM
                   SET CSM.[CSDATELASTACN_DATE] = @NowDateOnly
                  FROM [dbo].[Diary] DIA
                       INNER JOIN [dbo].[CaseMaster] CSM
                               ON CSM.[CSCODE] = DIA.[CASECODE]
                 WHERE DIA.[ACTIONID] = @ActionID

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0) 

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */

                /* Example of @NextActionsXML:
                   <NA>
                     <NextAction>
                       <Process>Y</Process>
                       <Decision>Do</Decision>
                       <Code>XXX</Code>
                     </NextAction>
                     <NextAction>
                       <Process>Y</Process>
                       <Decision>Do not</Decision>
                       <Code>YYY</Code>
                     </NextAction>
                     <NextAction>
                       <Process>N</Process>
                       <Decision>Do</Decision>
                       <Code>ZZZ</Code>
                     </NextAction>
                   </NA>
                */
                EXEC sp_xml_preparedocument @idoc OUTPUT, @NextActionsXML 

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

                IF (@Continue = 0)
                  BEGIN
                    SELECT @Continue = COUNT(1)
                      FROM OPENXML(@idoc, 'NA/NextAction', 2)
                              WITH(Process   varchar(1)    'Process',
                                   Decision  varchar(10)   'Decision',
                                   Code      varchar(5)    'Code') NA
                           INNER JOIN [dbo].[Diary] DIA
                                   ON DIA.[ACTIONID] = @ActionID
                           INNER JOIN [dbo].[TemplateActions] TAC
                                   ON TAC.[ACTIONCODE] = NA.[Code]
                                  AND TAC.[AssignActionTo] = 'P'
                           INNER JOIN [dbo].[Handlers] HAN
                                   ON HAN.[CODE] = @CurFee
                           INNER JOIN [dbo].[matters] MAT
                                      INNER JOIN [dbo].[Contacts] CNT
                                              ON CNT.[Code] = MAT.[ClientCode]
                                   ON MAT.[Code] = DIA.[CASECODE]
                                  AND RTRIM(ISNULL(MAT.[Partner], '')) = ''
                     WHERE NA.[Process] = 'Y'
                       AND NA.[Decision] = 'Do'

                    IF (@@ERROR <> 0)
                      BEGIN
                        /* this will eventually lead to a rollback */
                        SET @Continue = 1
                      END
                    -- END IF (@@ERROR <> 0)
                  END
                -- END IF (@Continue = 0) 

                IF (@Continue = 0)
                  BEGIN
                    /* top condition: (@Owner = 1) / NextActions */
                    -- Delete next actions flagged as "Do Not". First, Diary Delegations:
                    DELETE DSL
                      from OPENXML(@idoc, 'NA/NextAction', 2)
                              WITH(Process   varchar(1)    'Process',
                                   Decision  varchar(10)   'Decision',
                                   Code      varchar(5)    'Code') BLCH
                           inner join [dbo].[Diary] DIA
                                   ON DIA.[ACTIONID] = @ActionID
                           inner join [dbo].[Diary] DS
                                      inner join [dbo].[DiaryDelegations] DSL
                                              ON DSL.[ACTIONID] = DS.[ACTIONID]
                                   ON DS.[CASECODE] = DIA.[CASECODE]
                                  AND DS.[ACTIONCODE] = BLCH.[Code]
                                  AND DS.[STATUS] = 0
                     WHERE BLCH.[Process] = 'Y'
                       AND BLCH.[Decision] = 'Do not'

                    IF (@@ERROR <> 0)
                      BEGIN
                        /* this will eventually lead to a rollback */
                        SET @Continue = 1
                      END
                    -- END IF (@@ERROR <> 0)
                  END
                -- END IF (@Continue = 0) 

                IF (@Continue = 0)
                  BEGIN
                    /* top condition: (@Owner = 1) / NextActions */

                    -- Delete next actions flagged as "Do Not". Second, the actual Diary entries:
                    DELETE DS
                      from OPENXML(@idoc, 'NA/NextAction', 2)
                              WITH(Process   varchar(1)    'Process',
                                   Decision  varchar(10)   'Decision',
                                   Code      varchar(5)    'Code') BLCH
                           inner join [dbo].[Diary] DIA
                                   ON DIA.[ACTIONID] = @ActionID
                           inner join [dbo].[Diary] DS
                                   ON DS.[CASECODE] = DIA.[CASECODE]
                                  AND DS.[ACTIONCODE] = BLCH.[Code]
                                  AND DS.[STATUS] = 0
                     WHERE BLCH.[Process] = 'Y'
                       AND BLCH.[Decision] = 'Do not'

                    IF (@@ERROR <> 0)
                      BEGIN
                        /* this will eventually lead to a rollback */
                        SET @Continue = 1
                      END
                    -- END IF (@@ERROR <> 0)
                  END
                -- END IF (@Continue = 0) 

                /* Create new Actions based on the TemplateNextAction entries that are
                   flagged as "Do"s.
               
                   As this involves a number of steps, we'll use a temporary table @NA
                   (nextactions) that will hold the newly created Action IDs.
                   We can then use that as a basis for all following INSERT and UPDATE
                   statements ensuring that all Diary / DiaryDelegation (etc) entries are
                   correctly linked within this transaction.
                */
                IF (@Continue = 0)
                  BEGIN
                    /* top condition: (@Owner = 1) / NextActions */

                    SELECT @MaxActionID = IsNull(MAX(DIA.[ActionID]), 0)
                      FROM [dbo].[Diary] DIA

                    IF (@@ERROR <> 0)
                      BEGIN
                        /* this will eventually lead to a rollback */
                        SET @Continue = 1
                      END
                    -- END IF (@@ERROR <> 0)

                    IF (@Continue = 0)
                      BEGIN
                        INSERT
                          INTO @NA
                              ([ActionID],
                               [Code])
                        SELECT @MaxActionID,
                               BLCH.[Code]
                          from OPENXML(@idoc, 'NA/NextAction', 2)
                                  WITH(Process   varchar(1)    'Process',
                                       Decision  varchar(10)   'Decision',
                                       Code      varchar(5)    'Code') BLCH
                         WHERE BLCH.[Process] = 'Y'
                           AND BLCH.[Decision] = 'Do'

                        IF (@@ERROR <> 0)
                          BEGIN
                            /* this will eventually lead to a rollback */
                            SET @Continue = 1
                          END
                        -- END IF (@@ERROR <> 0)

                      END
                    -- END IF (@Continue = 0) 

                  END
                -- END IF (@Continue = 0) 

                EXEC sp_xml_removedocument @idoc

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)
    
                IF (@Continue = 0)
                  BEGIN
                    /* top condition: (@Owner = 1) / NextActions */

                    UPDATE @NA SET [ActionID] = [ActionID] + [ID]

                    IF (@@ERROR <> 0)
                      BEGIN
                        /* this will eventually lead to a rollback */
                        SET @Continue = 1
                      END
                    -- END IF (@@ERROR <> 0)

                  END
                -- END IF (@Continue = 0) 

                IF (@Continue = 0)
                  BEGIN
                    /* top condition: (@Owner = 1) / NextActions */

                    /* The following is based on the original Clarion
                       AddDiaryStep procedure (in CaseFun)

                       Based on the NextActions in the temporary @NA table this code will:
                       - Create new Diary entries,
                       - Add their DiaryDelegation records,
                       - Set the Linked Alarm
                       - Update CaseMaster with the last added Diary Work Type
           
                       ADDDIARYSTEP - START
                    */
                    INSERT
                      INTO [dbo].[Diary]
                          ([ActionID],
                           [CASECODE],
                           [DATE],
                           [STATUS],
                           [ACTIONSTATUS],
                           [ACTIONCODE],
                           [ACTIONTYPE],
                           [FnCode],
                           [TeamCode],
                           [Text1],
                           [DYStartTime],
                           [DYEndTime],
                           [DueDate],
                           [ImageNo],
                           [Publish],
                           [Priority],
                           [Highlighted],
                           [Billable],
                           [ProcessStatus],
                           [BillDescription],
                           [MILESTEONETYPE],
                           [ORGINALACTIONID],
                           [DraftBillNo])
                    SELECT NA.[ActionID],
                           DIA.[CASECODE] AS [CASECODE],
                           [dbo].[ky_WDGetDate](@Now,
                                                TNA.[WHENTYPE],
                                                TNA.[WHEN]) AS [DATE],
                           0 AS [STATUS],
                           TAC.[STATUS] AS [ACTIONSTATUS],
                           NA.[Code] AS [ACTIONCODE],
                           TAC.[ACTIONCATEGORY] AS [ACTIONTYPE],
                           CASE TAC.[AssignActionTo]
                                WHEN 'C' 
                                     THEN MAT.[FECode]
                                WHEN 'P' 
                                     THEN MAT.[Partner]
                                WHEN 'F' 
                                     THEN CNT.[FE]
                                WHEN 'O' 
                                     THEN TAC.[ASSIGNHANDLER]
                                ELSE @CurFee END AS [FnCode],
                           CASE TAC.[AssignTeamActionTo]
                                WHEN 'O' 
                                     THEN TAC.[ASSIGNTEAM]
                                ELSE HAN.[TEAM] END AS [TeamCode],
                           TAC.[DESC] as [Text1],
                           @NowClarionTime AS [DYStartTime],
                           @NowClarionEndTime AS [DYEndTime],
                           [dbo].[ky_WDGetDate](@NowDateOnly,
                                                TNA.[WHENTYPE],
                                                TNA.[WHEN]) AS [DueDate],
                           0 As [ImageNo],
                           TAC.[PUBLISHER] AS [Publish],
                           IsNull(SUBSTRING(TAC.[PRIORITY], 1, 1), 'N') As [Priority],
                           TAC.[HIGHLIGHTED] As [Highlighted],
                           CASE TAC.[BILLABLE] 
                                WHEN 'Y'
                                     THEN 1
                                ELSE 0 END As [Billable],
                           0 As [ProcessStatus],
                           TAC.[BILLDESCRIPTION] AS [BillDescription],
                           CASE TAC.[AssignActionTo]
                                WHEN 'C' 
                                     THEN MAT.[FECode]
                                WHEN 'P' 
                                     THEN MAT.[Partner]
                                WHEN 'F' 
                                     THEN CNT.[FE]
                                WHEN 'O' 
                                     THEN TAC.[ASSIGNHANDLER]
                                ELSE @CurFee END AS [MilestoneType],
                           @ActionID As [OriginalActionID],
                           0 As [DraftBillNo]               
                      from @NA NA
                           INNER JOIN [dbo].[Diary] DIA
                                   ON DIA.[ACTIONID] = @ActionID
                           INNER JOIN [dbo].[TemplateActions] TAC
                                   ON TAC.[ACTIONCODE] = NA.[Code]
                           INNER JOIN [dbo].[TemplateNextActions] TNA
                                   ON TNA.[ACTION] = DIA.[ACTIONCODE]
                                  AND TNA.[NEXTACTION] = NA.[Code]
                           INNER JOIN [dbo].[Handlers] HAN
                                   ON HAN.[CODE] = @CurFee
                           INNER JOIN [dbo].[matters] MAT
                                      INNER JOIN [dbo].[Contacts] CNT
                                              ON CNT.[Code] = MAT.[ClientCode]
                                   ON MAT.[Code] = DIA.[CASECODE]

                    IF (@@ERROR <> 0)
                      BEGIN
                        /* this will eventually lead to a rollback */
                        SET @Continue = 1
                      END
                    -- END IF (@@ERROR <> 0)

                    IF (@Continue = 0)
                      BEGIN
                        /* top condition: (@Owner = 1) / NextActions / AddDiaryStep */
    
                        INSERT
                          INTO [dbo].[DiaryDelegations]
                              ([ACTIONID],
                               [HANDLER],
                               [TEAM],
                               [DATE],
                               [TIME],
                               [DUEDATE],
                               [DUETIME],
                               [STATUS],
                               [OWNER],
                               [DELEGATE],
                               [ACTIONTYPE],
                               [DelType],
                               [FromHandler])
                        SELECT DIA.[ACTIONID] AS [ACTIONID],
                               CASE WHEN @TakeOwnership = 1 THEN @CurFee ELSE DIA.[FNCODE] END AS [HANDLER],
                               CASE WHEN @TakeOwnership = 1 THEN @CurTeam ELSE DIA.[TEAMCODE] END AS [TEAM],
                               DIA.[DUEDATE] AS [DATE],
                               @NowClarionTime as [TIME],
                               DIA.[DUEDATE] AS [DUEDATE],
                               @NowClarionTime AS [DUETIME],
                               DIA.[STATUS] AS [STATUS],
                               'Y' AS [OWNER],
                               DIA.[FNCODE] AS [DELEGATE],
                               DIA.[ACTIONTYPE] AS [ACTIONTYPE],
                               'Created' AS [DelType],
                               DIA.[FNCODE] AS [FromHandler]
                          FROM @NA NA
                               INNER JOIN [dbo].[Diary] DIA
                                       ON DIA.[ACTIONID] = NA.[ActionID]

                        IF (@@ERROR <> 0)
                          BEGIN
                            /* this will eventually lead to a rollback */
                            SET @Continue = 1
                          END
                        -- END IF (@@ERROR <> 0)

                      END
                    -- END IF (@Continue = 0)

                    IF (@Continue = 0)
                      BEGIN
                        /* top condition: (@Owner = 1) / NextActions / AddDiaryStep */

                        INSERT
                          INTO [dbo].[Alarms]
                              ([ID],
                               [HandlerCode],
                               [MatterRef],
                               [LinkID],
                               [OriginalDate],
                               [DeferredDate],
                               [Title],
                               [Message],
                               [Type],
                               [Status])
                        SELECT convert(varchar(38), newid()) AS [ID],
                               DEL.[HANDLER] as [Handler],
                               DIA.[CASECODE] As [MatterRef],
                               convert(varchar(50), NA.[ActionID]) AS [LinkID],
                               DIA.[DATE] As [OriginalDate],
                               DIA.[DATE] As [DeferredDate],
                               CASE WHEN DIA.[ActionType] <> 'E' 
                                         THEN RTRIM(substring(DIA.[TEXT1], 1, 255))
                                    WHEN DIA.[ProcessType] = 'I'
                                         THEN RTRIM(substring(  'Email From:'
                                                              + RTrim([dbo].[ky_GetNameFromEmailAddress](DIA.[EMAILADDRESS]))
                                                              + ' - '
                                                              + substring(DIA.[Subject],
                                                                          1,
                                                                          255),
                                                    1,
                                                    255))
                                    ELSE RTRIM(substring(  'Email To:'
                                                         + RTrim(substring(DIA.[AddressTo],
                                                                           1,
                                                                           255))
                                                         + ' - '
                                                         + substring(DIA.[Subject],
                                                                     1,
                                                                     255),
                                               1,
                                               255)) end AS [Title],
                               RTrim(substring(DIA.[Text1], 1, 8000)) AS [Message],
                               @AlarmType As [Type],
                               ALS.[StatusID] AS [Status]
                          from @NA NA
                               INNER JOIN [dbo].[Diary] DIA
                                       ON DIA.[ACTIONID] = NA.[ActionID]
                               INNER JOIN [dbo].[DiaryDelegations] DEL
                                       ON DEL.[ACTIONID] = NA.[ActionID]
                               INNER JOIN [dbo].[AlarmStatus] ALS
                                       ON ALS.[Description] = 'Attention'

                        IF (@@ERROR <> 0)
                          BEGIN
                            /* this will eventually lead to a rollback */
                            SET @Continue = 1
                          END
                        -- END IF (@@ERROR <> 0)
              
                      END
                    -- END IF (@Continue = 0)

                    IF (@Continue = 0)
                      BEGIN
                        /* top condition: (@Owner = 1) / NextActions / AddDiaryStep */

                        UPDATE CSM
                           SET CSM.[CSWKTCODE] = TAC.[WKTCODE]
                          FROM ( SELECT MAX(NA.[ActionID]) AS [MaxActionID]
                                   FROM @NA NA) LAID
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[TemplateActions] TAC
                                                  ON TAC.[ACTIONCODE] = DIA.[ACTIONCODE]
                                       ON DIA.[ACTIONID] = LAID.[MaxActionID]
                               INNER JOIN [dbo].[CaseMaster] CSM
                                       ON CSM.[CSCODE] = DIA.[CASECODE]

                      END
                    -- END IF (@Continue = 0)

                    /* 
                       ADDDIARYSTEP - END
                    */

                  END
                -- END IF (@Continue = 0) 
              END
            -- END IF (@Continue = 0) 

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */
            
                /* Complete the diary delegations */
                UPDATE DEL
                   SET DEL.[STATUS] = 1,
                       DEL.[HANDLER] = CASE WHEN @TakeOwnership = 1 THEN @CurFee ELSE DEL.[HANDLER] END,
                       DEL.[TEAM] = CASE WHEN @TakeOwnership = 1 THEN @CurTeam ELSE DEL.[TEAM] END,
                       DEL.[DelType] = CASE WHEN (@TakeOwnership = 1) AND (DEL.[DelType] = 'Released') AND (DEL.[FromHandler] = @CurFee) THEN 'Completed' ELSE DEL.[DelType] END
                  FROM [dbo].[DiaryDelegations] DEL
                 WHERE DEL.[ACTIONID] = @ActionID
                   AND IsNull(DEL.[STATUS], 0) <> 1

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0) 

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 1) */

                /* Write new 'Completed' row to Diary Delegations */
                INSERT
                  INTO [dbo].[DiaryDelegations]
                      ([ACTIONID],
                       [HANDLER],
                       [TEAM],
                       [FromHandler],
                       [ReturnedBy],
                       [DELEGATE],
                       [DATE],
                       [TIME],
                       [STATUS],
                       [DELEGATESTATUS],
                       [DelType])
                SELECT DIA.[ACTIONID],
                       DIA.[FNCODE],
                       DIA.[TEAMCODE],
                       DIA.[FNCODE],
                       DIA.[FNCODE],
                       DIA.FNCODE,
                       @NowDateOnly,
                       @NowClarionTime,
                       1,
                       1,
                       'Completed'
                  FROM [dbo].[Diary] DIA
                 WHERE DIA.[ACTIONID] = @ActionID

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0) 
          END
        -- END IF (@Owner = 1)

        IF (@Owner = 0)  
          BEGIN
      
            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 0) */

                /* Return Action */
                UPDATE DIA
                   SET DIA.[DELEGATEDFNR] = @LocDel,
                       DIA.[PROCESSSTATUS] = 0,
                       DIA.[DELEGATIONSTATUS] = CASE WHEN @LocDel = DIA.[FNCODE] 
                                                          THEN 2
                                                     ELSE DIA.[DELEGATIONSTATUS] END
                  FROM [dbo].[Diary] DIA
                 WHERE DIA.[ACTIONID] = @ActionID

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0)

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 0) */

                IF (@WorkProcess = 0)
                  BEGIN
                    INSERT
                      INTO [dbo].[DiaryDelegations]
                          ([ACTIONID],
                           [HANDLER],
                           [TEAM],
                           [DATE],
                           [TIME],
                           [TIMER],
                           [DUETIME],
                           [REVIEW],
                           [STATUS],
                           [OWNER],
                           [DELEGATE],
                           [DELEGATESTATUS],
                           [ActionType],
                           [FromHandler],
                           [ReturnedBy],
                           [DelType])
                    SELECT DEL.[ACTIONID],
                           @CurFee As [HANDLER],
                           @CurTeam AS [TEAM],
                           DEL.[DATE],
                           DEL.[TIME],
                           '' AS [TIMER],
                           '' AS [DUETIME],
                           '' AS [REVIEW],
                           1 AS [STATUS],
                           '' AS [OWNER],
                           @CurFee AS [DELEGATE],
                           1 As [DELEGATESTATUS],
                           '' AS [ActionType],
                           @CurFee AS [FromHandler],
                           @CurFee AS [ReturnedBy],
                           'Processed' AS [DelType]
                      FROM [dbo].[DiaryDelegations] DEL
                     WHERE DEL.[ACTIONID] = @ActionID
                       AND DEL.[HANDLER] = @LocDel
                       AND DEL.[STATUS] = 0

                    UPDATE DIA
                       SET DIA.[WorkProcess] = 1
                      FROM [dbo].[Diary] DIA
                     WHERE DIA.[ActionID] = @ActionID
                  END
                 
                INSERT
                  INTO [dbo].[DiaryDelegations]
                      ([ACTIONID],
                       [ActionType],
                       [DATE],
                       [DATER],
                       [DELEGATE],
                       [DELEGATESTATUS],
                       [DUEDATE],
                       [DUETIME],
                       [DelType],
                       [FromHandler],
                       [HANDLER],
                       [OWNER],
                       [REVIEW],
                       [ReturnedBy],
                       [STATUS],
                       [TEAM],
                       [TIME],
                       [TIMER])
                SELECT DEL.[ACTIONID],
                       DEL.[ActionType],
                       DEL.[DATE],
                       @NowDateOnly AS [DATER],
                       DEL.[DELEGATE],
                       2 As [DELEGATESTATUS],
                       DEL.[DUEDATE],
                       DEL.[DUETIME],
                       'Returned' AS [DelType],
                       DEL.[FromHandler],
                       DEL.[HANDLER],
                       DEL.[OWNER],
                       DEL.[REVIEW],
                       @CurFee AS [ReturnedBy],
                       1 AS [STATUS],
                       DEL.[TEAM],
                       DEL.[TIME],
                       @NowClarionTime AS [TIMER]
                  FROM [dbo].[DiaryDelegations] DEL
                 WHERE DEL.[ACTIONID] = @ActionID
                   AND DEL.[HANDLER] = @LocDel
                   AND DEL.[STATUS] = 0

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)

              END
            -- END IF (@Continue = 0)

            IF (@Continue = 0)
              BEGIN
                /* top condition: (@Owner = 0) */

                UPDATE DEL
                   SET DEL.[DELEGATESTATUS] = 2,
                       DEL.[DATER] = @NowDateOnly,
                       DEL.[TIMER] = @NowClarionTime,
                       DEL.[ReturnedBy] = @CurFee
                  FROM [dbo].[DiaryDelegations] DEL
                 WHERE DEL.[ACTIONID] = @ActionID
                   AND DEL.[HANDLER] = @LocDel
                   AND DEL.[STATUS] = 0

                IF (@@ERROR <> 0)
                  BEGIN
                    /* this will eventually lead to a rollback */
                    SET @Continue = 1
                  END
                -- END IF (@@ERROR <> 0)
               
              END
            -- END IF (@Continue = 0)

          END
        -- END IF (@Owner = 0)

      END 
    -- END IF (@Continue = 0) 
  END
END TRY
BEGIN CATCH
  SET @Continue = 1
END CATCH

IF (@Continue = 0)
  BEGIN
    COMMIT
  END
ELSE -- ELSE (IF (@Continue = 0))
  BEGIN
    ROLLBACK
  END
-- END IF (@Continue = 0) 

END

/**************************************************************************************
 *
 * ky_WDAllCaseAssociates2
 *
 * Lists all case associates in the system
 *
 **************************************************************************************/
GO

create function 
       [dbo].[ky_WDAllCaseAssociates2]()
RETURNS @SR TABLE
       ([Type]           varchar(20),
        [Name]           varchar(200),
        [Address]        varchar(200),
        [Phone]          varchar(30),
        [Mobile]         varchar(30),
        [Fax]            varchar(30),
        [Email]          varchar(100))
AS
BEGIN

    INSERT
      INTO @SR
          ([Name],
           [Address],
           [Phone],
           [Mobile],
           [Fax],
           [Email],
           [Type])
    SELECT CASE WHEN RTrim(IsNull(CAN.[NAME], '')) = ''
                     THEN CONVERT(varchar(200), RTRIM(ISNULL(CAN.[LEGALNAME], '')))
                ELSE CONVERT(varchar(200), RTRIM(ISNULL(CAN.[NAME], ''))) END AS [NAME],
           CASE WHEN RTrim(IsNull(CAN.[ADDRESS], '')) = ''
                     THEN SUBSTRING(CAN.[LegalAddress], 1, 200)
                ELSE SUBSTRING(CAN.[Address], 1, 200) END AS [ADDRESS],
           CASE WHEN RTrim(IsNull(CAN.[PHONENO], '')) <> ''
                     THEN convert(varchar(30), RTrim(IsNull(CAN.[PHONENO], '')))
                WHEN RTrim(IsNull(CAN.[MOBILENO], '')) <> ''
                     THEN convert(varchar(30), RTrim(IsNull(CAN.[MOBILENO], '')))
                ELSE convert(varchar(30), RTrim(IsNull(CAN.[FAXNO], ''))) END AS [PHONE],
           convert(varchar(30), RTrim(IsNull(CAN.[MOBILENO], ''))) AS [MOBILE],
           convert(varchar(30), RTrim(IsNull(CAN.[FAXNO], ''))) AS [FAX],
           convert(varchar(100), RTrim(IsNull(CAN.[EMAIL], ''))) AS [EMAIL],
           convert(varchar(20), RTrim(IsNull(CAN.[TYPE], ''))) AS [TYPE]
      FROM [dbo].[CaseAssociatesNames] CAN
    UNION
    SELECT CONVERT(varchar(200), RTRIM(ISNULL(CAN.[NAME], ''))) AS [NAME],
           SUBSTRING(CAN.[Address], 1, 200) AS [ADDRESS],
           CASE WHEN RTrim(IsNull(CAN.[PHONENO], '')) <> ''
                     THEN convert(varchar(30), RTrim(IsNull(CAN.[PHONENO], '')))
                WHEN RTrim(IsNull(CAN.[MOBILENO], '')) <> ''
                     THEN convert(varchar(30), RTrim(IsNull(CAN.[MOBILENO], '')))
                ELSE convert(varchar(30), RTrim(IsNull(CAN.[FAXNO], ''))) END AS [PHONE],
           convert(varchar(30), RTrim(IsNull(CAN.[MOBILENO], ''))) AS [MOBILE],
           convert(varchar(30), RTrim(IsNull(CAN.[FAXNO], ''))) AS [FAX],
           convert(varchar(100), RTrim(IsNull(CAN.[EMAIL], ''))) AS [EMAIL],
           convert(varchar(20), RTrim(IsNull(CAN.[TYPE], ''))) AS [TYPE]
      FROM (SELECT CAC.[NAMECODE],
                   CAC.[KEYID],
                   CASE WHEN RTrim(IsNull(CAC.[NAME], '')) = ''
                             THEN RTRIM(IsNull(CNN.[NAME], ''))
                        ELSE RTrim(IsNull(CAC.[NAME], '')) END AS [NAME],
                   CASE WHEN RTrim(IsNull(CAC.[ADDRESS], '')) = ''
                             THEN RTRIM(IsNull(CNN.[ADDRESS], ''))
                        ELSE RTrim(IsNull(CAC.[ADDRESS], '')) END AS [ADDRESS],
                   CASE WHEN RTrim(IsNull(CAC.[PHONE], '')) = ''
                             THEN RTRIM(IsNull(CNN.[PHONENO], ''))
                        ELSE RTrim(IsNull(CAC.[PHONE], '')) END AS [PHONENO],
                   RTrim(IsNull(CAC.[MOBILE], '')) AS [MOBILENO],
                   CASE WHEN RTrim(IsNull(CAC.[FAX], '')) = ''
                             THEN RTRIM(IsNull(CNN.[FAXNO], ''))
                        ELSE RTrim(IsNull(CAC.[FAX], '')) END AS [FAXNO],
                   CASE WHEN RTrim(IsNull(CAC.[EMAIL], '')) = ''
                             THEN RTRIM(IsNull(CNN.[EMAIL], ''))
                        ELSE RTrim(IsNull(CAC.[EMAIL], '')) END AS [EMAIL],
                   convert(varchar(20), RTrim(IsNull(CNN.[TYPE], ''))) AS [TYPE]
              FROM [dbo].[CaseAssociatesContacts] CAC
                   LEFT OUTER JOIN [dbo].[CaseAssociatesNames] CNN
                                ON CNN.[CODE] = CAC.[NAMECODE]) CAN

UPDATE @SR
   SET [Phone] = [dbo].[ky_HeuristicPhoneNumber]([Address], [Phone]),
       [Mobile] = [dbo].[ky_HeuristicPhoneNumber]([Address], [Mobile]),
       [Fax] = [dbo].[ky_HeuristicPhoneNumber]([Address], [Fax])

  RETURN
END

GO

Create Procedure
       [dbo].[ky_WDCreateTranscription]
      (@TrackReference int,
       @extension varchar(10))
AS
BEGIN

  DECLARE @FilePath varchar(255)

  DECLARE @slashpos int
  DECLARE @lastslash int

  DECLARE @dotpos int
  DECLARE @lastdot int
  
  SELECT @FilePath = RTRIM(ISNULL(DAT.[FilePath], ''))
    FROM [dbo].[DiaryAttachments] DAT
   WHERE DAT.[TRACKREFERENCE] = @TrackReference
   
  SET @lastslash = 0
  SET @slashpos = CHARINDEX('\', @FilePath, 1)

  WHILE (@slashpos > 0)
    BEGIN
      SET @lastslash = @slashpos
      SET @slashpos = CHARINDEX('\', @FilePath, @slashpos + 1)
    END

  SET @lastdot = 0
  SET @dotpos = CHARINDEX('.', @FilePath, @lastdot + 1)

  WHILE (@dotpos > 0)
    BEGIN
      SET @lastdot = @dotpos
      SET @dotpos = CHARINDEX('.', @FilePath, @dotpos + 1)
    END
  
  IF (@lastdot > 0)
    SET @FilePath = LEFT(@FilePath, @lastdot - 1)
   
  INSERT
    INTO [dbo].[DiaryAttachments]
        ([DIARYID],
         [TRACKREFERENCE],
         [CASECODE],
         [NAME],
         [DOCUMENT],
         [FILEPATH],
         [TYPE],
         [DOCCLASS],
         [DATEENTERED],
         [ENTEREDBY],
         [LASTACCESSDATE],
         [LASTACCESSBY],
         [SYSTEM],
         [DICTATIONFILE],
         [Source])
  SELECT DAT.[DIARYID],
         NTR.[TrackReference],
         DAT.[CASECODE],
         DAT.[NAME],
         DAT.[DOCUMENT],
         @FilePath + '_' + CONVERT(varchar(12), NTR.[TrackReference]) + '.' + @extension,
         UPPER(LEFT(@extension, 3)) AS [TYPE],
         DAT.[DOCCLASS],
         GETDATE(),
         DAT.[ENTEREDBY],
         GETDATE(),
         DAT.[LASTACCESSBY],
         'N' As [SYSTEM],
         'N' AS [DictationFile],
         'Transcriptions' AS [Source]
    FROM (SELECT ISNULL(Max(MTR.[TrackReference]), 0) + 1 AS [TrackReference]
            FROM [dbo].[DiaryAttachments] MTR) NTR
         INNER JOIN [dbo].[DiaryAttachments] DAT
                 ON DAT.[TRACKREFERENCE] = @TrackReference
                 
END
GO

Create Procedure
       [dbo].[ky_WDCompleteTranscription]
      (@TrackReference int)
AS
BEGIN

  DECLARE @DiaryID int
  DECLARE @Handler varchar(10)
  DECLARE @status int
  
  UPDATE DIA
     SET DIA.[TxmDate] = GETDATE()
    FROM [dbo].[DiaryAttachments] DAT
         INNER JOIN [dbo].[diary] DIA
                 ON DIA.[ACTIONID] = DAT.[DIARYID]
   WHERE DAT.[TRACKREFERENCE] = @TrackReference

  SELECT @DiaryID = DAT.[DiaryID],
         @Handler = CASE WHEN RTRIM(ISNULL(DIA.[DELEGATEDFNR], '')) <> ''
                              THEN DIA.[DELEGATEDFNR] 
                         ELSE DIA.[FNCODE] END,
         @status = DIA.[STATUS]
    FROM [dbo].[DiaryAttachments] DAT
         INNER JOIN [dbo].[Diary] DIA
                 ON DIA.[ACTIONID] = DAT.[DIARYID]
   WHERE DAT.[TRACKREFERENCE] = @TrackReference
  
  IF (@status = 0)
    BEGIN
      EXEC [dbo].[ky_WDActionComplete2] @DiaryId, @Handler, '<NA/>', 0
    END
    
END
GO

/*******************************************************************************************
 *
 * ky_WDGetTAList2
 *    Returns a list of Template Actions for the appropriate workflow. If there is a default Note
 *    action or a default Phone action, this will be listed at the top of the list, regardless of
 *    the workflow.
 *
 *    Supersedes ky_WDGetTAList. Better handling of missing Templates
 *
 *******************************************************************************************/
CREATE FUNCTION
       [dbo].[ky_WDGetTAList2]
      (@matter           varchar(20),
       @WKTCode          varchar(10),
       @ActionCategory   varchar(1))
RETURNS @TA TABLE
       ([WORKTYPE]       varchar(10),
        [SEQ]            int,
        [ACTIONCODE]     varchar(20),
        [DESC]           varchar(255),
        [ActionCategory] varchar(1),
        [Highlighted]    varchar(1),
        [Publisher]      varchar(1),
        [Billable]       varchar(1),
        [Priority]       varchar(10),
        [Status]         varchar(5))
AS
BEGIN
  
  DECLARE @DEFAULTTA varchar(20)
     
  SET @ActionCategory = RTRIM(ISNULL(@ActionCategory, ''))
  SET @WKTCODE = RTRIM(ISNULL(@WKTCODE, ''))

  IF (@ActionCategory = 'N')
    BEGIN
      SELECT @DEFAULTTA = DA.[Action]  
        from [dbo].[DefaultNoteTA] DA
    END

  IF (@ActionCategory = 'T')
    BEGIN
      SELECT @DEFAULTTA = DA.[Action]  
        from [dbo].[DefaultPhoneTA] DA
    END

  INSERT
    INTO @TA
        ([WORKTYPE],
         [SEQ],
         [ACTIONCODE],
         [DESC],
         [ActionCategory],
         [Highlighted],
         [Publisher],
         [Billable],
         [Priority],
         [Status])
  SELECT RTRIM(ISNULL(TA.[WKTCODE], '')) AS [WORKTYPE],
         IsNull(AWT.[SEQ], 999999),
         RTRIM(ISNULL(TA.[ACTIONCODE], '')) AS [ACTIONCODE],
         REPLACE(REPLACE(RTRIM(ISNULL(TA.[DESC], '')), CHAR(13) + CHAR(10), ' '), CHAR(10), ' ') AS [DESC],
         ISNULL(TA.[ACTIONCATEGORY], 'A') AS [ACTIONCATEGORY],
         CASE WHEN ISNULL(TA.[HIGHLIGHTED], 'N') = 'Y'
                   THEN 'Y' 
              ELSE 'N' END AS [HIGHLIGHTED],
         ISNULL(TA.[PUBLISHER], 'N') AS [PUBLISHER],
         ISNULL(TA.[BILLABLE], 'N') AS [BILLABLE],
         RTRIM(ISNULL(TA.[PRIORITY], 'Normal')) AS [PRIORITY],
         RTRIM(ISNULL(TA.[STATUS], 'NA')) AS [STATUS]
    FROM [dbo].[TemplateActions] TA
         LEFT OUTER JOIN [dbo].[ActionWorkTypes] AWT
                 ON AWT.[ACTIONCODE] = TA.[ACTIONCODE]
         LEFT OUTER JOIN [dbo].[CaseMaster] CSM
                      ON CSM.[CSCODE] = @matter
         LEFT OUTER JOIN [dbo].[Templates] T
                      ON T.[WKCODE] = TA.[WKTCODE]
   WHERE (   (    @WKTCODE = ''
              AND TA.[WKTCODE] = CSM.[CSWKTCODE])
          OR (    @WKTCODE <> ''
              AND TA.[WKTCODE] = @WKTCODE)
          OR TA.[ACTIONCODE] = @DEFAULTTA)
     AND (   (    T.[WKCODE] = TA.[WKTCODE]
              AND AWT.[WORKTYPE] = T.[WKCODE])
          OR (    T.[WKCODE] IS NULL
              AND AWT.[WORKTYPE] IS NULL))
ORDER BY CASE WHEN @ActionCategory = ''
                   THEN 0
              WHEN @ActionCategory = TA.[ActionCategory]
                   THEN CASE WHEN TA.[ACTIONCODE] = @DEFAULTTA 
                                  THEN 1
                             WHEN @ActionCategory = 'N' AND TA.[DESC] LIKE '%note%'
                                  THEN 2
                             WHEN @ActionCategory = 'P' AND TA.[DESC] LIKE '%appointment%'
                                  THEN 2
                             WHEN @ActionCategory = 'T' AND TA.[DESC] LIKE '%phone%'
                                  THEN 2
                             ELSE 3 END
              ELSE 4 END,
         ISNULL(AWT.[SEQ], 999999)
RETURN
END

GO

DECLARE @COMMAND varchar(8000)
DECLARE @ChargeField varchar(10)
DECLARE @Units int

IF EXISTS(SELECT *
            FROM sysobjects SO
                 inner join syscolumns SC
                         ON SC.[id] = SO.[id]
           WHERE so.[name] = 'TimeDayBook'
             AND so.[xtype] = 'U'
             AND UPPER(sc.[name]) = 'CHARGE')
BEGIN
  SET @ChargeField = 'Charge'
END
ELSE
BEGIN
  SET @ChargeField = 'CHARGED'
END

IF EXISTS(SELECT *
            FROM sysobjects SO
                 inner join syscolumns SC
                         ON SC.[id] = SO.[id]
           WHERE so.[name] = 'TimeDayBook'
             AND so.[xtype] = 'U'
             AND UPPER(sc.[name]) = 'UNITS')
BEGIN
  SET @Units = 1
END
ELSE
BEGIN
  SET @Units = 0
END
  
SET @COMMAND = '
/**************************************************************************************
 *
 * ky_WDSetTimeDayBookRateCharge
 *
 * Sets the Rate and Charge values according to the Charge Out Rates indicators and/or
 *     the Task Code used in the TimeDayBook.
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDSetTimeDayBookRateCharge]
      (@recordid int)
AS
BEGIN
UPDATE TDB
   SET TDB.[Time] = Ceiling(TDB.[Time] / IsNull(TUT.[NumValue], 1)) * IsNull(TUT.[NumValue], 1),
       TDB.[RATE] = Case When IsNull(MAT.[ChargeOutRateInd], ''N'') = ''Y''
                              THEN IsNull(MCR.[Rate], 0)
                         When IsNull(CON.[ChargeOutRateInd], ''N'') = ''Y''
                              THEN IsNull(CCR.[Rate], 0)
                         ELSE IsNull(FEP.[ChargeFct], 1.00) * FER.[Rate] END,
       TDB.[' + @ChargeField + '] = Convert(numeric(7, 2),
                              (  (  Ceiling(TDB.[Time] / IsNull(TUT.[NumValue], 1))
                                  * IsNull(TUT.[NumValue], 1)
                                  * Case When IsNull(MAT.[ChargeOutRateInd], ''N'') = ''Y''
                                              THEN IsNull(MCR.[Rate], 0)
                                         When IsNull(CON.[ChargeOutRateInd], ''N'') = ''Y''
                                              THEN IsNull(CCR.[Rate], 0)
                                         ELSE IsNull(FEP.[ChargeFct], 1.00) * FER.[Rate] END)
                               / 60))' +
       CASE WHEN @Units = 0
                 THEN ''
            ELSE ',
       TDB.[Units] = Ceiling(TDB.[Time] / IsNull(TUT.[NumValue], 1))' END + '
  from [dbo].[TimeDayBook] TDB
       LEFT OUTER JOIN [dbo].[matters] MAT
                       INNER JOIN [dbo].[contacts] CON
                               ON CON.[Code] = MAT.[ClientCode]
                    ON MAT.[code] = TDB.[MATTER]
       OUTER APPLY (SELECT MCOR.[Rate]
                      FROM [dbo].[MatterChargeOutRates] MCOR
                     WHERE MCOR.[FeCode] = TDB.[FEEEARN]
                       AND MCOR.[MatterCode] = TDB.[MATTER]) MCR
       OUTER APPLY (SELECT CCOR.[Rate]
                      FROM [dbo].[ClientChargeOutRates] CCOR
                     WHERE CCOR.[FeCode] = TDB.[FEEEARN]
                       AND CCOR.[ClientCode] = TDB.[MATTER]) CCR
       CROSS APPLY (SELECT CASE IsNull(MAT.[ChargeLevel], 3)
                                WHEN 1 THEN IsNull(HDL.[RATE1], 0)
                                WHEN 2 THEN IsNull(HDL.[RATE2], 0)
                                WHEN 3 THEN IsNull(HDL.[RATE3], 0)
                                WHEN 4 THEN IsNull(HDL.[RATE4], 0)
                                WHEN 5 THEN IsNull(HDL.[RATE5], 0)
                                ELSE IsNull(HDL.[RATE], 0) END AS [RATE]
                      FROM [dbo].[Handlers] HDL
                     WHERE HDL.[CODE] = TDB.[FEEEARN]) FER' + 
       CASE WHEN @Units = 0
                 THEN '
       OUTER APPLY (SELECT 1 AS [NumValue]) TUT'
            ELSE '
       OUTER APPLY (SELECT CTN.[NumValue]
                      FROM [dbo].[CtrlNum] CTN
                     WHERE CTN.[Code] = ''TimeUnit'') TUT' END + '
       OUTER APPLY (SELECT CASE IsNull(TAC.[ChargeLevel], ''C'')
                                WHEN ''A'' THEN 0.33
                                WHEN ''B'' THEN 0.67
                                WHEN ''C'' THEN 1.00
                                WHEN ''D'' THEN 1.33
                                WHEN ''E'' THEN 1.67
                                ELSE 1.00 END AS [ChargeFct]
                      FROM [dbo].[TasksCodes] TAC
                     WHERE TAC.[CODE] = CASE RTrim(IsNull(TDB.MATTER, ''''))
                                             WHEN '''' THEN TDB.[NCCCODE]
                                             ELSE TDB.[TASK] END) FEP
 WHERE TDB.[RECORDID] = @RECORDID

END'

EXEC (@COMMAND)

GO


DECLARE @COMMAND varchar(8000)
DECLARE @ChargeField varchar(10)
DECLARE @Units int
DECLARE @COMLEN int

select @COMLEN = CASE WHEN sc.[length] > 16
                           THEN sc.[length]
                      ELSE 2000 end
  from sysobjects so
       inner join syscolumns sc
               on sc.[id] = so.[id]
              and sc.[name] = 'COMMENT'
 where so.[name] = 'TimeEntry'
 
IF EXISTS(SELECT *
            FROM sysobjects SO
                 inner join syscolumns SC
                         ON SC.[id] = SO.[id]
           WHERE so.[name] = 'TimeEntry'
             AND so.[xtype] = 'U'
             AND UPPER(sc.[name]) = 'CHARGE')
BEGIN
  SET @ChargeField = 'CHARGE'
END
ELSE
BEGIN
  SET @ChargeField = 'CHARGED'
END

IF EXISTS(SELECT *
            FROM sysobjects SO
                 inner join syscolumns SC
                         ON SC.[id] = SO.[id]
           WHERE so.[name] = 'TimeEntry'
             AND so.[xtype] = 'U'
             AND UPPER(sc.[name]) = 'UNITS')
BEGIN
  SET @Units = 1
END
ELSE
BEGIN
  SET @Units = 0
END
  
SET @COMMAND = '
/**************************************************************************************
 *
 * ky_WDUpdateTimeEntry
 *
 * Sets the Rate and Charge values according to the Charge Out Rates indicators and/or
 *     the Task Code used in the TimeEntry Table.
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDUpdateTimeEntry]
      (@recordid int,
       @fe varchar(10),
       @task varchar(10),
       @comment varchar(' + CONVERT(varchar(4), @COMLEN) + '),
       @sysrate int,
       @rate decimal(7, 2),
       @date datetime,
       @time int,
       @REC_IRR varchar(1))
AS
BEGIN
UPDATE TIE
   SET TIE.[FEEEARN] = RTRIM(ISNULL(@fe, '''')),
       TIE.[TASK] = RTRIM(ISNULL(@task, '''')),
       TIE.[COMMENT] = RTRIM(ISNULL(@COMMENT, '''')),
       TIE.[Date] = @Date,
       TIE.[Time] = IsNull(TUT.[NumValue], 1) * Ceiling(IsNull(@time, 0) / IsNull(TUT.[NumValue], 1)),
       TIE.[REC_IRR] = @REC_IRR,
       TIE.[RATE] = Case When @sysrate = 0
                              THEN @rate
                         When IsNull(MAT.[ChargeOutRateInd], ''N'') = ''Y''
                              THEN IsNull(MCR.[Rate], 0)
                         When IsNull(CON.[ChargeOutRateInd], ''N'') = ''Y''
                              THEN IsNull(CCR.[Rate], 0)
                         ELSE IsNull(FEP.[ChargeFct], 1.00) * FER.[Rate] END,
       TIE.[' + @ChargeField + '] = Convert(numeric(7, 2),
                              (  (  IsNull(TUT.[NumValue], 1) 
                                  * Ceiling(IsNull(@time, 0) / IsNull(TUT.[NumValue], 1))
                                  * Case When @sysrate = 0
                                              THEN @rate
                                         When IsNull(MAT.[ChargeOutRateInd], ''N'') = ''Y''
                                              THEN IsNull(MCR.[Rate], 0)
                                         When IsNull(CON.[ChargeOutRateInd], ''N'') = ''Y''
                                              THEN IsNull(CCR.[Rate], 0)
                                         ELSE IsNull(FEP.[ChargeFct], 1.00) * FER.[Rate] END)
                               / 60))' +
       CASE WHEN @Units = 0
                 THEN ''
            ELSE ',
       TIE.[Units] = Ceiling(IsNull(@time, 0) / IsNull(TUT.[NumValue], 1))' END + '
  from [dbo].[TimeEntry] TIE
       LEFT OUTER JOIN [dbo].[matters] MAT
                       INNER JOIN [dbo].[contacts] CON
                               ON CON.[Code] = MAT.[ClientCode]
                    ON MAT.[code] = TIE.[MATTER]
       OUTER APPLY (SELECT MCOR.[Rate]
                      FROM [dbo].[MatterChargeOutRates] MCOR
                     WHERE MCOR.[FeCode] = TIE.[FEEEARN]
                       AND MCOR.[MatterCode] = TIE.[MATTER]) MCR
       OUTER APPLY (SELECT CCOR.[Rate]
                      FROM [dbo].[ClientChargeOutRates] CCOR
                     WHERE CCOR.[FeCode] = TIE.[FEEEARN]
                       AND CCOR.[ClientCode] = TIE.[MATTER]) CCR
       CROSS APPLY (SELECT CASE IsNull(MAT.[ChargeLevel], 3)
                                WHEN 1 THEN IsNull(HDL.[RATE1], 0)
                                WHEN 2 THEN IsNull(HDL.[RATE2], 0)
                                WHEN 3 THEN IsNull(HDL.[RATE3], 0)
                                WHEN 4 THEN IsNull(HDL.[RATE4], 0)
                                WHEN 5 THEN IsNull(HDL.[RATE5], 0)
                                ELSE IsNull(HDL.[RATE], 0) END AS [RATE]
                      FROM [dbo].[Handlers] HDL
                     WHERE HDL.[CODE] = TIE.[FEEEARN]) FER' + 
       CASE WHEN @Units = 0
                 THEN '
       OUTER APPLY (SELECT 1 AS [NumValue]) TUT'
            ELSE '
       OUTER APPLY (SELECT CTN.[NumValue]
                      FROM [dbo].[CtrlNum] CTN
                     WHERE CTN.[Code] = ''TimeUnit'') TUT' END + '
       OUTER APPLY (SELECT CASE IsNull(TAC.[ChargeLevel], ''C'')
                                WHEN ''A'' THEN 0.33
                                WHEN ''B'' THEN 0.67
                                WHEN ''C'' THEN 1.00
                                WHEN ''D'' THEN 1.33
                                WHEN ''E'' THEN 1.67
                                ELSE 1.00 END AS [ChargeFct]
                      FROM [dbo].[TasksCodes] TAC
                     WHERE TAC.[CODE] = CASE RTrim(IsNull(TIE.MATTER, ''''))
                                             WHEN '''' THEN TIE.[NCCODE]
                                             ELSE TIE.[TASK] END) FEP
 WHERE TIE.[RECORDID] = @RECORDID

INSERT
  INTO [dbo].[TimeEntryLog]
      ([TimeId],
       [FromMatter],
       [ToMatter],
       [TrDate],
       [TrBy])
SELECT TIE.[RECORDID],
       TIE.[MATTER],
       TIE.[MATTER],
       getDate(),
       ''UP''
  FROM [dbo].[TimeEntry] TIE
 WHERE TIE.[RECORDID] = @RECORDID
 
END'

EXEC (@COMMAND)

GO

DECLARE @COMMAND varchar(8000)
DECLARE @ChargeField varchar(10)
DECLARE @Units int
DECLARE @COMLEN int

select @COMLEN = CASE WHEN sc.[length] > 16
                           THEN sc.[length]
                      ELSE 2000 end
  from sysobjects so
       inner join syscolumns sc
               on sc.[id] = so.[id]
              and sc.[name] = 'COMMENT'
 where so.[name] = 'TimeEntry'
 
IF EXISTS(SELECT *
            FROM sysobjects SO
                 inner join syscolumns SC
                         ON SC.[id] = SO.[id]
           WHERE so.[name] = 'TimeEntry'
             AND so.[xtype] = 'U'
             AND UPPER(sc.[name]) = 'CHARGE')
BEGIN
  SET @ChargeField = 'CHARGE'
END
ELSE
BEGIN
  SET @ChargeField = 'CHARGED'
END

IF EXISTS(SELECT *
            FROM sysobjects SO
                 inner join syscolumns SC
                         ON SC.[id] = SO.[id]
           WHERE so.[name] = 'TimeEntry'
             AND so.[xtype] = 'U'
             AND UPPER(sc.[name]) = 'UNITS')
BEGIN
  SET @Units = 1
END
ELSE
BEGIN
  SET @Units = 0
END
  
SET @COMMAND = '
/**************************************************************************************
 *
 * ky_WDUpdateTimeEntry2
 *
 * Sets the Rate and Charge values according to the Charge Out Rates indicators and/or
 *     the Task Code used in the TimeEntry Table.
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDUpdateTimeEntry2]
      (@recordid int,
       @fe varchar(10),
       @task varchar(10),
       @comment varchar(' + CONVERT(varchar(4), @COMLEN) + '),
       @sysrate int,
       @rate decimal(7, 2),
       @date datetime,
       @time int,
       @REC_IRR varchar(1),
       @TimeOrCharge varchar(1),
       @charge decimal(7, 2))
AS
BEGIN

SET @TimeOrCharge = ISNULL(@TimeOrCharge, ''T'')
SET @rate = ISNULL(@rate, 0)
SET @charge = ISNULL(@charge, 0)
SET @time = ISNULL(@time, 0)

UPDATE TIE
   SET TIE.[FEEEARN] = RTRIM(ISNULL(@fe, '''')),
       TIE.[TASK] = RTRIM(ISNULL(@task, '''')),
       TIE.[COMMENT] = RTRIM(ISNULL(@COMMENT, '''')),
       TIE.[Date] = @Date,
       TIE.[Time] = CASE WHEN (@TimeOrCharge <> ''C'') OR (@time <> 0)
                              THEN IsNull(TUT.[NumValue], 1) * Ceiling(IsNull(@time, 0) / IsNull(TUT.[NumValue], 1))
                         ELSE 1 END,
       TIE.[REC_IRR] = @REC_IRR,
       TIE.[RATE] = CASE WHEN @TimeOrCharge <> ''C''
                              THEN Case When @sysrate = 0
                                             THEN @rate
                                        When IsNull(MAT.[ChargeOutRateInd], ''N'') = ''Y''
                                             THEN IsNull(MCR.[Rate], 0)
                                        When IsNull(CON.[ChargeOutRateInd], ''N'') = ''Y''
                                             THEN IsNull(CCR.[Rate], 0)
                                        ELSE IsNull(FEP.[ChargeFct], 1.00) * FER.[Rate] END
                         ELSE @charge end,
       TIE.[' + @ChargeField + '] = CASE WHEN @TimeOrCharge = ''C''
                                THEN @charge
                           ELSE Convert(numeric(7, 2),
                                        (  (  IsNull(TUT.[NumValue], 1) 
                                            * Ceiling(IsNull(@time, 0) / IsNull(TUT.[NumValue], 1))
                                         * Case When @sysrate = 0
                                                     THEN @rate
                                                When IsNull(MAT.[ChargeOutRateInd], ''N'') = ''Y''
                                                     THEN IsNull(MCR.[Rate], 0)
                                                When IsNull(CON.[ChargeOutRateInd], ''N'') = ''Y''
                                                     THEN IsNull(CCR.[Rate], 0)
                                                ELSE IsNull(FEP.[ChargeFct], 1.00) * FER.[Rate] END)
                                         / 60)) END,
       TIE.[TIMEORCHARGE] = CASE WHEN @TimeOrCharge = ''C''
                                      THEN ''C''
                                 ELSE ''T'' END' +
       CASE WHEN @Units = 0
                 THEN ''
            ELSE ',
       TIE.[Units] = Ceiling(IsNull(@time, 0) / IsNull(TUT.[NumValue], 1))' END + '
  from [dbo].[TimeEntry] TIE
       LEFT OUTER JOIN [dbo].[matters] MAT
                       INNER JOIN [dbo].[contacts] CON
                               ON CON.[Code] = MAT.[ClientCode]
                    ON MAT.[code] = TIE.[MATTER]
       OUTER APPLY (SELECT MCOR.[Rate]
                      FROM [dbo].[MatterChargeOutRates] MCOR
                     WHERE MCOR.[FeCode] = TIE.[FEEEARN]
                       AND MCOR.[MatterCode] = TIE.[MATTER]) MCR
       OUTER APPLY (SELECT CCOR.[Rate]
                      FROM [dbo].[ClientChargeOutRates] CCOR
                     WHERE CCOR.[FeCode] = TIE.[FEEEARN]
                       AND CCOR.[ClientCode] = TIE.[MATTER]) CCR
       CROSS APPLY (SELECT CASE IsNull(MAT.[ChargeLevel], 3)
                                WHEN 1 THEN IsNull(HDL.[RATE1], 0)
                                WHEN 2 THEN IsNull(HDL.[RATE2], 0)
                                WHEN 3 THEN IsNull(HDL.[RATE3], 0)
                                WHEN 4 THEN IsNull(HDL.[RATE4], 0)
                                WHEN 5 THEN IsNull(HDL.[RATE5], 0)
                                ELSE IsNull(HDL.[RATE], 0) END AS [RATE]
                      FROM [dbo].[Handlers] HDL
                     WHERE HDL.[CODE] = TIE.[FEEEARN]) FER' + 
       CASE WHEN @Units = 0
                 THEN '
       OUTER APPLY (SELECT 1 AS [NumValue]) TUT'
            ELSE '
       OUTER APPLY (SELECT CTN.[NumValue]
                      FROM [dbo].[CtrlNum] CTN
                     WHERE CTN.[Code] = ''TimeUnit'') TUT' END + '
       OUTER APPLY (SELECT CASE IsNull(TAC.[ChargeLevel], ''C'')
                                WHEN ''A'' THEN 0.33
                                WHEN ''B'' THEN 0.67
                                WHEN ''C'' THEN 1.00
                                WHEN ''D'' THEN 1.33
                                WHEN ''E'' THEN 1.67
                                ELSE 1.00 END AS [ChargeFct]
                      FROM [dbo].[TasksCodes] TAC
                     WHERE TAC.[CODE] = CASE RTrim(IsNull(TIE.MATTER, ''''))
                                             WHEN '''' THEN TIE.[NCCODE]
                                             ELSE TIE.[TASK] END) FEP
 WHERE TIE.[RECORDID] = @RECORDID

INSERT
  INTO [dbo].[TimeEntryLog]
      ([TimeId],
       [FromMatter],
       [ToMatter],
       [TrDate],
       [TrBy])
SELECT TIE.[RECORDID],
       TIE.[MATTER],
       TIE.[MATTER],
       getDate(),
       ''UP''
  FROM [dbo].[TimeEntry] TIE
 WHERE TIE.[RECORDID] = @RECORDID
 
END'

EXEC (@COMMAND)

GO

/**************************************************************************************
 *
 * ky_WDTaskListSet2Rev
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE FUNCTION
       [dbo].[ky_WDTaskListSet2Rev]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @Search            varchar(100))
RETURNS @TL TABLE
       ([taskid]              int               IDENTITY(1, 1),
        [ActionID]            int,
        [AssignNo]            int)
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@Search <> '%')
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
  END
ELSE
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
  END

  DELETE @TL
   WHERE [taskid] < @startno

  DELETE @TL
   WHERE [taskid] > (@startno + @count - 1)

  RETURN
END
GO

/**************************************************************************************
 *
 * ky_WDTaskListSet2
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE FUNCTION
       [dbo].[ky_WDTaskListSet2]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @Search            varchar(100))
RETURNS @TL TABLE
       ([taskid]              int               IDENTITY(1, 1),
        [ActionID]            int,
        [AssignNo]            int)
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@Search <> '%')
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
  END
ELSE
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@startno + @count - 1)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
  END

  DELETE @TL
   WHERE [taskid] < @startno

  DELETE @TL
   WHERE [taskid] > (@startno + @count - 1)

  RETURN
END
GO

/**************************************************************************************
 *
 * ky_WDTaskListByHighlightSet2
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE FUNCTION
       [dbo].[ky_WDTaskListByHighlightSet2]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Highlighted       varchar(1),
       @Priority          varchar(1),
       @Search            varchar(100))
RETURNS @TL TABLE
       ([taskid]              int               IDENTITY(1, 1),
        [ActionID]            int,
        [AssignNo]            int)
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime
DECLARE @maxresult int

SET @Highlighted = RTrim(IsNull(@Highlighted, ''))
SET @Priority = RTrim(IsNull(@Priority, ''))

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

SET @maxresult = (@startno + @count - 1)
  
IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@outstanding = 0)
  BEGIN
    IF (@handler = '') AND (@team = '') 
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD 
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
      END
    ELSE IF (@handler = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[TEAM] = @team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
      END
    ELSE IF (@team = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[handler] = @handler
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
      END
    ELSE
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[HANDLER] = @handler
                       AND DEL.[TEAM] = @Team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
        END
  END
ELSE
  BEGIN
    IF (@handler = '') AND (@team = '') 
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
      END
    ELSE IF (@handler = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[TEAM] = @team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
      END
    ELSE IF (@team = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[HANDLER] = @Handler
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
      END
    ELSE
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[HANDLER] = @handler
                       AND DEL.[TEAM] = @Team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE],
                   OD.[TIME],
                   OD.[AssignNo],
                   OD.[ActionID]
        END
  END

  DELETE @TL
   WHERE [taskid] < @startno

  DELETE @TL
   WHERE [taskid] > (@maxresult)

  RETURN
END
GO

/**************************************************************************************
 *
 * ky_WDTaskListByActionTypeSet2
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE FUNCTION
       [dbo].[ky_WDTaskListByActionTypeSet2]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Search            varchar(100))
RETURNS @TL TABLE
       ([taskid]              int               IDENTITY(1, 1),
        [ActionID]            int,
        [AssignNo]            int)
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime
DECLARE @maxresult int

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

SET @maxresult = (@startno + @count - 1)
  
IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@Search <> '%')
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @Handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
  END
ELSE
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @Handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE],
                       OD.[TIME],
                       OD.[AssignNo],
                       OD.[ActionID]
            END
      END
  END
    
  DELETE @TL
   WHERE [taskid] < @startno

  DELETE @TL
   WHERE [taskid] > (@maxresult)

  RETURN
END

GO

/**************************************************************************************
 *
 * ky_WDTaskListByHighlightSet2Rev
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE FUNCTION
       [dbo].[ky_WDTaskListByHighlightSet2Rev]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Highlighted       varchar(1),
       @Priority          varchar(1),
       @Search            varchar(100))
RETURNS @TL TABLE
       ([taskid]              int               IDENTITY(1, 1),
        [ActionID]            int,
        [AssignNo]            int)
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime
DECLARE @maxresult int

SET @Highlighted = RTrim(IsNull(@Highlighted, ''))
SET @Priority = RTrim(IsNull(@Priority, ''))

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

SET @maxresult = (@startno + @count - 1)
  
IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@outstanding = 0)
  BEGIN
    IF (@handler = '') AND (@team = '') 
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD 
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
      END
    ELSE IF (@handler = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[TEAM] = @team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
      END
    ELSE IF (@team = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[handler] = @handler
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
      END
    ELSE
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[HANDLER] = @handler
                       AND DEL.[TEAM] = @Team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
        END
  END
ELSE
  BEGIN
    IF (@handler = '') AND (@team = '') 
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
      END
    ELSE IF (@handler = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[TEAM] = @team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
      END
    ELSE IF (@team = '')
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[HANDLER] = @Handler
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
      END
    ELSE
      BEGIN
        INSERT
          INTO @TL
              ([ActionID],
               [AssignNo])
            SELECT TOP (@maxresult)
                   OD.[ActionID],
                   OD.[AssignNo]
              FROM (SELECT DEL.[ActionID],
                           DEL.[AssignNo],
                           DEL.[DATE],
                           DEL.[TIME]
                      FROM [dbo].[DiaryDelegations] DEL
                           INNER JOIN [dbo].[Diary] DIA
                                      INNER JOIN [dbo].[matters] MS
                                                 INNER JOIN [dbo].[contacts] CTS
                                                         ON CTS.[CODE] = MS.[ClientCode]
                                              ON MS.[Code] = DIA.[CaseCode]
                                   ON DIA.[ActionID] = DEL.[ActionID]
                                  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
                                                                                THEN 'Y'
                                                                           ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
                                  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
                                                                 THEN DIA.[PRIORITY] 
                                                            ELSE @Priority  end
                                  AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                       OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                       OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                       OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                       OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                     WHERE DEL.[HANDLER] = @handler
                       AND DEL.[TEAM] = @Team
                       AND DEL.[DATE] >= @startdate
                       AND DEL.[DATE] < @enddate
                       AND DEL.[STATUS] = 0
                       AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                       AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                            OR ISNULL(@ActionType, '') = '')) OD
          ORDER BY OD.[DATE] DESC,
                   OD.[TIME] DESC,
                   OD.[AssignNo] DESC,
                   OD.[ActionID] DESC
        END
  END

  DELETE @TL
   WHERE [taskid] < @startno

  DELETE @TL
   WHERE [taskid] > (@maxresult)

  RETURN
END
GO

/**************************************************************************************
 *
 * ky_WDTaskListByActionTypeSet2Rev
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE FUNCTION
       [dbo].[ky_WDTaskListByActionTypeSet2Rev]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Search            varchar(100))
RETURNS @TL TABLE
       ([taskid]              int               IDENTITY(1, 1),
        [ActionID]            int,
        [AssignNo]            int)
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime
DECLARE @maxresult int

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

SET @maxresult = (@startno + @count - 1)
  
IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@Search <> '%')
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @Handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                                      AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                                           OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                                           OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                                           OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                                           OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
  END
ELSE
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[handler] = @handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@handler = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[TEAM] = @team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE IF (@team = '')
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @Handler
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
          END
        ELSE
          BEGIN
            INSERT
              INTO @TL
                  ([ActionID],
                   [AssignNo])
                SELECT TOP (@maxresult)
                       OD.[ActionID],
                       OD.[AssignNo]
                  FROM (SELECT DEL.[ActionID],
                               DEL.[AssignNo],
                               DEL.[DATE],
                               DEL.[TIME]
                          FROM [dbo].[DiaryDelegations] DEL
                               INNER JOIN [dbo].[Diary] DIA
                                          INNER JOIN [dbo].[matters] MS
                                                     INNER JOIN [dbo].[contacts] CTS
                                                             ON CTS.[CODE] = MS.[ClientCode]
                                                  ON MS.[Code] = DIA.[CaseCode]
                                       ON DIA.[ActionID] = DEL.[ActionID]
                         WHERE DEL.[HANDLER] = @handler
                           AND DEL.[TEAM] = @Team
                           AND DEL.[DATE] >= @startdate
                           AND DEL.[DATE] < @enddate
                           AND DEL.[STATUS] = 0
                           AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
                           AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
                                OR ISNULL(@ActionType, '') = '')) OD
              ORDER BY OD.[DATE] DESC,
                       OD.[TIME] DESC,
                       OD.[AssignNo] DESC,
                       OD.[ActionID] DESC
            END
      END
  END
    
  DELETE @TL
   WHERE [taskid] < @startno

  DELETE @TL
   WHERE [taskid] > (@maxresult)

  RETURN
END

GO

/**************************************************************************************
 *
 * ky_WDTaskListByActionTypePage2
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListByActionTypePage2]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Search            varchar(100))
AS
BEGIN
  SELECT TSK.[CaseCode],
         TSK.[Action],
         TSK.[Subject],
         RTrim(IsNull(CTC.[Name], '')) AS [ClientName],
         RTrim(IsNull(MAT.[Description], '')) AS [MatterDescription],
         Convert(varchar, TSK.[DelegatedDateTime], 106) As [DATE],
         Convert(varchar, TSK.[DelegatedDateTime], 108) As [TIME],
         TSK.[Status],
         TSK.[ActionType],
         TSK.[Priority],
         TSK.[DelegateStatus],
         TSK.[ImageNo],
         TSK.[Handler],
         TSK.[Team],
         TSK.[FromHandler],
         TSK.[ActionID],
         TSK.[DiaryStatus],
         TSK.[Highlighted],
         TSK.[Attachments],
         TSK.[DiaryDate],
         TSK.[DiaryDDate],
         TSK.[FNCode],
         TSK.[TeamCode],
         TSK.[WorkProcess],
         TSK.[DelegationStatus],
         TSK.[ActionCode],
         TSK.[DURATION],
         TSK.[CVDuration],
         TSK.[DELEGATEDDATETIME],
         TSK.[AssignNo]
    FROM (  SELECT RTrim(IsNull(DIA.[CaseCode], '')) As [CaseCode],
                   RTrim([dbo].[ky_removespuriouswhitespace2](substring(DIA.[TEXT1], 1, 500), 500)) As [Action],
                   RTrim([dbo].[ky_removespuriouswhitespace](substring(DIA.[SUBJECT], 1, 150), 150)) As [Subject],
                   DEL.[Status],
                   IsNull(DIA.[ActionType], '') As [ActionType],
                   IsNull(DIA.[Priority], '') As [Priority],
                   IsNull(DEL.[DELEGATESTATUS], 0) As [DelegateStatus],
                   DIA.[ImageNo],
                   RTRIM(IsNull(DEL.[Handler], '')) As [Handler],
                   RTRIM(IsNull(DEL.[Team], '')) As [Team],
                   RTRIM(IsNull(DEL.[FromHandler], '')) As [FromHandler],
                   DIA.[ActionID] As [ActionID],
                   IsNull(DIA.[Status], 0) As [DiaryStatus],
                   IsNull(DIA.[Highlighted], '') As [Highlighted],
                   IsNull(DIA.[Attachments], '') As [Attachments],
                   Convert(varchar, DIA.[DATE], 106) As [DiaryDate],
                   DIA.[DATE] As [DiaryDDate],
                   RTrim(IsNull(DIA.[FNCode], '')) AS [FNCode],
                   IsNull(DIA.[TeamCode], '') AS [TeamCode],
                   IsNull(DIA.[WorkProcess], 0) AS [WorkProcess],
                   Convert(int, IsNull(DIA.[DelegationStatus], 0)) AS [DelegationStatus],
                   RTrim(IsNull(DIA.[ACTIONCODE], '')) AS [ActionCode],
                   CASE WHEN DIA.[ACTIONTYPE] = 'D' AND DIA.[IMAGENO] > 0
                             THEN 'Length: ' + Convert(varchar, [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DIA.[DATE], DIA.[IMAGENO]), 108)
                        ELSE '' END AS [DURATION],
                   [dbo].[ky_getDuration](DIA.[DYSTARTTIME], DIA.[DYENDTIME], 30) AS [CVDuration],
                   [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DEL.[DATE], DEL.[TIME]) AS [DelegatedDateTime],
                   DEL.[AssignNo]
              FROM [dbo].[ky_WDTaskListByActionTypeSet2]
                  (@Handler,
                   @Team,
                   @startdateT,
                   @enddateT,
                   @outstanding,
                   @startno,
                   @count,
                   @ActionType,
                   @Search) TLS
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = TLS.[ActionID]
                          --AND DIA.[ActionType] = @ActionType
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                   INNER JOIN [dbo].[DiaryDelegations] DEL
                           ON DEL.[AssignNo] = TLS.[AssignNo]) TSK
         INNER JOIN [dbo].[matters] MAT
                    INNER JOIN [dbo].[contacts] CTC
                            ON CTC.[CODE] = MAT.[CLIENTCODE]
                 ON MAT.[Code] = TSK.[CASECODE]
ORDER BY TSK.[DelegatedDateTime]

END
GO

/**************************************************************************************
 *
 * ky_WDTaskListByActionTypePage2Rev
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListByActionTypePage2Rev]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Search            varchar(100))
AS
BEGIN
  SELECT TSK.[CaseCode],
         TSK.[Action],
         TSK.[Subject],
         RTrim(IsNull(CTC.[Name], '')) AS [ClientName],
         RTrim(IsNull(MAT.[Description], '')) AS [MatterDescription],
         Convert(varchar, TSK.[DelegatedDateTime], 106) As [DATE],
         Convert(varchar, TSK.[DelegatedDateTime], 108) As [TIME],
         TSK.[Status],
         TSK.[ActionType],
         TSK.[Priority],
         TSK.[DelegateStatus],
         TSK.[ImageNo],
         TSK.[Handler],
         TSK.[Team],
         TSK.[FromHandler],
         TSK.[ActionID],
         TSK.[DiaryStatus],
         TSK.[Highlighted],
         TSK.[Attachments],
         TSK.[DiaryDate],
         TSK.[DiaryDDate],
         TSK.[FNCode],
         TSK.[TeamCode],
         TSK.[WorkProcess],
         TSK.[DelegationStatus],
         TSK.[ActionCode],
         TSK.[DURATION],
         TSK.[CVDuration],
         TSK.[DELEGATEDDATETIME],
         TSK.[AssignNo]
    FROM (  SELECT RTrim(IsNull(DIA.[CaseCode], '')) As [CaseCode],
                   RTrim([dbo].[ky_removespuriouswhitespace2](substring(DIA.[TEXT1], 1, 500), 500)) As [Action],
                   RTrim([dbo].[ky_removespuriouswhitespace](substring(DIA.[SUBJECT], 1, 150), 150)) As [Subject],
                   DEL.[Status],
                   IsNull(DIA.[ActionType], '') As [ActionType],
                   IsNull(DIA.[Priority], '') As [Priority],
                   IsNull(DEL.[DELEGATESTATUS], 0) As [DelegateStatus],
                   DIA.[ImageNo],
                   RTRIM(IsNull(DEL.[Handler], '')) As [Handler],
                   RTRIM(IsNull(DEL.[Team], '')) As [Team],
                   RTRIM(IsNull(DEL.[FromHandler], '')) As [FromHandler],
                   DIA.[ActionID] As [ActionID],
                   IsNull(DIA.[Status], 0) As [DiaryStatus],
                   IsNull(DIA.[Highlighted], '') As [Highlighted],
                   IsNull(DIA.[Attachments], '') As [Attachments],
                   Convert(varchar, DIA.[DATE], 106) As [DiaryDate],
                   DIA.[DATE] As [DiaryDDate],
                   RTrim(IsNull(DIA.[FNCode], '')) AS [FNCode],
                   IsNull(DIA.[TeamCode], '') AS [TeamCode],
                   IsNull(DIA.[WorkProcess], 0) AS [WorkProcess],
                   Convert(int, IsNull(DIA.[DelegationStatus], 0)) AS [DelegationStatus],
                   RTrim(IsNull(DIA.[ACTIONCODE], '')) AS [ActionCode],
                   CASE WHEN DIA.[ACTIONTYPE] = 'D' AND DIA.[IMAGENO] > 0
                             THEN 'Length: ' + Convert(varchar, [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DIA.[DATE], DIA.[IMAGENO]), 108)
                        ELSE '' END AS [DURATION],
                   [dbo].[ky_getDuration](DIA.[DYSTARTTIME], DIA.[DYENDTIME], 30) AS [CVDuration],
                   [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DEL.[DATE], DEL.[TIME]) AS [DelegatedDateTime],
                   DEL.[AssignNo]
              FROM [dbo].[ky_WDTaskListByActionTypeSet2Rev]
                  (@Handler,
                   @Team,
                   @startdateT,
                   @enddateT,
                   @outstanding,
                   @startno,
                   @count,
                   @ActionType,
                   @Search) TLS
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = TLS.[ActionID]
                          --AND DIA.[ActionType] = @ActionType
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                   INNER JOIN [dbo].[DiaryDelegations] DEL
                           ON DEL.[AssignNo] = TLS.[AssignNo]) TSK
         INNER JOIN [dbo].[matters] MAT
                    INNER JOIN [dbo].[contacts] CTC
                            ON CTC.[CODE] = MAT.[CLIENTCODE]
                 ON MAT.[Code] = TSK.[CASECODE]
ORDER BY TSK.[DelegatedDateTime] DESC

END
GO

/**************************************************************************************
 *
 * ky_WDTaskListByHighlightPage2
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListByHighlightPage2]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Highlighted       varchar(1),
       @Priority          varchar(1),
       @Search            varchar(100))
AS
BEGIN
  SELECT TSK.[CaseCode],
         TSK.[Action],
         TSK.[Subject],
         RTrim(IsNull(CTC.[Name], '')) AS [ClientName],
         RTrim(IsNull(MAT.[Description], '')) AS [MatterDescription],
         Convert(varchar, TSK.[DelegatedDateTime], 106) As [DATE],
         Convert(varchar, TSK.[DelegatedDateTime], 108) As [TIME],
         TSK.[Status],
         TSK.[ActionType],
         TSK.[Priority],
         TSK.[DelegateStatus],
         TSK.[ImageNo],
         TSK.[Handler],
         TSK.[Team],
         TSK.[FromHandler],
         TSK.[ActionID],
         TSK.[DiaryStatus],
         TSK.[Highlighted],
         TSK.[Attachments],
         TSK.[DiaryDate],
         TSK.[DiaryDDate],
         TSK.[FNCode],
         TSK.[TeamCode],
         TSK.[WorkProcess],
         TSK.[DelegationStatus],
         TSK.[ActionCode],
         TSK.[DURATION],
         TSK.[CVDuration],
         TSK.[DELEGATEDDATETIME],
         TSK.[AssignNo]
    FROM (  SELECT RTrim(IsNull(DIA.[CaseCode], '')) As [CaseCode],
                   RTrim([dbo].[ky_removespuriouswhitespace2](substring(DIA.[TEXT1], 1, 500), 500)) As [Action],
                   RTrim([dbo].[ky_removespuriouswhitespace](substring(DIA.[SUBJECT], 1, 150), 150)) As [Subject],
                   DEL.[Status],
                   IsNull(DIA.[ActionType], '') As [ActionType],
                   IsNull(DIA.[Priority], '') As [Priority],
                   IsNull(DEL.[DELEGATESTATUS], 0) As [DelegateStatus],
                   DIA.[ImageNo],
                   RTRIM(IsNull(DEL.[Handler], '')) As [Handler],
                   RTRIM(IsNull(DEL.[Team], '')) As [Team],
                   RTRIM(IsNull(DEL.[FromHandler], '')) As [FromHandler],
                   DIA.[ActionID] As [ActionID],
                   IsNull(DIA.[Status], 0) As [DiaryStatus],
                   IsNull(DIA.[Highlighted], '') As [Highlighted],
                   IsNull(DIA.[Attachments], '') As [Attachments],
                   Convert(varchar, DIA.[DATE], 106) As [DiaryDate],
                   DIA.[DATE] As [DiaryDDate],
                   RTrim(IsNull(DIA.[FNCode], '')) AS [FNCode],
                   IsNull(DIA.[TeamCode], '') AS [TeamCode],
                   IsNull(DIA.[WorkProcess], 0) AS [WorkProcess],
                   Convert(int, IsNull(DIA.[DelegationStatus], 0)) AS [DelegationStatus],
                   RTrim(IsNull(DIA.[ACTIONCODE], '')) AS [ActionCode],
                   CASE WHEN DIA.[ACTIONTYPE] = 'D' AND DIA.[IMAGENO] > 0
                             THEN 'Length: ' + Convert(varchar, [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DIA.[DATE], DIA.[IMAGENO]), 108)
                        ELSE '' END AS [DURATION],
                   [dbo].[ky_getDuration](DIA.[DYSTARTTIME], DIA.[DYENDTIME], 30) AS [CVDuration],
                   [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DEL.[DATE], DEL.[TIME]) AS [DelegatedDateTime],
                   DEL.[AssignNo]
              FROM [dbo].[ky_WDTaskListByHighlightSet2]
                  (@Handler,
                   @Team,
                   @startdateT,
                   @enddateT,
                   @outstanding,
                   @startno,
                   @count,
                   @ActionType,
                   @Highlighted,
                   @Priority,
                   @Search) TLS
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = TLS.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                   INNER JOIN [dbo].[DiaryDelegations] DEL
                           ON DEL.[AssignNo] = TLS.[AssignNo]) TSK
         INNER JOIN [dbo].[matters] MAT
                    INNER JOIN [dbo].[contacts] CTC
                            ON CTC.[CODE] = MAT.[CLIENTCODE]
                 ON MAT.[Code] = TSK.[CASECODE]
ORDER BY TSK.[DelegatedDateTime]

END
GO

/**************************************************************************************
 *
 * ky_WDTaskListByHighlightPage2Rev
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListByHighlightPage2Rev]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @ActionType        varchar(500),
       @Highlighted       varchar(1),
       @Priority          varchar(1),
       @Search            varchar(100))
AS
BEGIN
  SELECT TSK.[CaseCode],
         TSK.[Action],
         TSK.[Subject],
         RTrim(IsNull(CTC.[Name], '')) AS [ClientName],
         RTrim(IsNull(MAT.[Description], '')) AS [MatterDescription],
         Convert(varchar, TSK.[DelegatedDateTime], 106) As [DATE],
         Convert(varchar, TSK.[DelegatedDateTime], 108) As [TIME],
         TSK.[Status],
         TSK.[ActionType],
         TSK.[Priority],
         TSK.[DelegateStatus],
         TSK.[ImageNo],
         TSK.[Handler],
         TSK.[Team],
         TSK.[FromHandler],
         TSK.[ActionID],
         TSK.[DiaryStatus],
         TSK.[Highlighted],
         TSK.[Attachments],
         TSK.[DiaryDate],
         TSK.[DiaryDDate],
         TSK.[FNCode],
         TSK.[TeamCode],
         TSK.[WorkProcess],
         TSK.[DelegationStatus],
         TSK.[ActionCode],
         TSK.[DURATION],
         TSK.[CVDuration],
         TSK.[DELEGATEDDATETIME],
         TSK.[AssignNo]
    FROM (  SELECT RTrim(IsNull(DIA.[CaseCode], '')) As [CaseCode],
                   RTrim([dbo].[ky_removespuriouswhitespace2](substring(DIA.[TEXT1], 1, 500), 500)) As [Action],
                   RTrim([dbo].[ky_removespuriouswhitespace](substring(DIA.[SUBJECT], 1, 150), 150)) As [Subject],
                   DEL.[Status],
                   IsNull(DIA.[ActionType], '') As [ActionType],
                   IsNull(DIA.[Priority], '') As [Priority],
                   IsNull(DEL.[DELEGATESTATUS], 0) As [DelegateStatus],
                   DIA.[ImageNo],
                   RTRIM(IsNull(DEL.[Handler], '')) As [Handler],
                   RTRIM(IsNull(DEL.[Team], '')) As [Team],
                   RTRIM(IsNull(DEL.[FromHandler], '')) As [FromHandler],
                   DIA.[ActionID] As [ActionID],
                   IsNull(DIA.[Status], 0) As [DiaryStatus],
                   IsNull(DIA.[Highlighted], '') As [Highlighted],
                   IsNull(DIA.[Attachments], '') As [Attachments],
                   Convert(varchar, DIA.[DATE], 106) As [DiaryDate],
                   DIA.[DATE] As [DiaryDDate],
                   RTrim(IsNull(DIA.[FNCode], '')) AS [FNCode],
                   IsNull(DIA.[TeamCode], '') AS [TeamCode],
                   IsNull(DIA.[WorkProcess], 0) AS [WorkProcess],
                   Convert(int, IsNull(DIA.[DelegationStatus], 0)) AS [DelegationStatus],
                   RTrim(IsNull(DIA.[ACTIONCODE], '')) AS [ActionCode],
                   CASE WHEN DIA.[ACTIONTYPE] = 'D' AND DIA.[IMAGENO] > 0
                             THEN 'Length: ' + Convert(varchar, [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DIA.[DATE], DIA.[IMAGENO]), 108)
                        ELSE '' END AS [DURATION],
                   [dbo].[ky_getDuration](DIA.[DYSTARTTIME], DIA.[DYENDTIME], 30) AS [CVDuration],
                   [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DEL.[DATE], DEL.[TIME]) AS [DelegatedDateTime],
                   DEL.[AssignNo]
              FROM [dbo].[ky_WDTaskListByHighlightSet2Rev]
                  (@Handler,
                   @Team,
                   @startdateT,
                   @enddateT,
                   @outstanding,
                   @startno,
                   @count,
                   @ActionType,
                   @Highlighted,
                   @Priority,
                   @Search) TLS
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = TLS.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                   INNER JOIN [dbo].[DiaryDelegations] DEL
                           ON DEL.[AssignNo] = TLS.[AssignNo]) TSK
         INNER JOIN [dbo].[matters] MAT
                    INNER JOIN [dbo].[contacts] CTC
                            ON CTC.[CODE] = MAT.[CLIENTCODE]
                 ON MAT.[Code] = TSK.[CASECODE]
ORDER BY TSK.[DelegatedDateTime] DESC

END
GO

/**************************************************************************************
 *
 * ky_WDTaskListPage
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListPage]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @Search            varchar(100))
AS
BEGIN
  SELECT TSK.[CaseCode],
         TSK.[Action],
         TSK.[Subject],
         RTrim(IsNull(CTC.[Name], '')) AS [ClientName],
         RTrim(IsNull(MAT.[Description], '')) AS [MatterDescription],
         Convert(varchar, TSK.[DelegatedDateTime], 106) As [DATE],
         Convert(varchar, TSK.[DelegatedDateTime], 108) As [TIME],
         TSK.[Status],
         TSK.[ActionType],
         TSK.[Priority],
         TSK.[DelegateStatus],
         TSK.[ImageNo],
         TSK.[Handler],
         TSK.[Team],
         TSK.[FromHandler],
         TSK.[ActionID],
         TSK.[DiaryStatus],
         TSK.[Highlighted],
         TSK.[Attachments],
         TSK.[DiaryDate],
         TSK.[DiaryDDate],
         TSK.[FNCode],
         TSK.[TeamCode],
         TSK.[WorkProcess],
         TSK.[DelegationStatus],
         TSK.[ActionCode],
         TSK.[DURATION],
         TSK.[CVDuration],
         TSK.[DELEGATEDDATETIME],
         TSK.[AssignNo]
    FROM (  SELECT RTrim(IsNull(DIA.[CaseCode], '')) As [CaseCode],
                   RTrim([dbo].[ky_removespuriouswhitespace](substring(DIA.[TEXT1], 1, 120), 80)) As [Action],
                   RTRIM([dbo].[ky_removespuriouswhitespace](substring(DIA.[SUBJECT], 1, 150), 80)) As [Subject],
                   DEL.[Status],
                   IsNull(DIA.[ActionType], '') As [ActionType],
                   IsNull(DIA.[Priority], '') As [Priority],
                   IsNull(DEL.[DELEGATESTATUS], 0) As [DelegateStatus],
                   DIA.[ImageNo],
                   RTRIM(IsNull(DEL.[Handler], '')) As [Handler],
                   RTRIM(IsNull(DEL.[Team], '')) As [Team],
                   RTRIM(IsNull(DEL.[FromHandler], '')) As [FromHandler],
                   DIA.[ActionID] As [ActionID],
                   IsNull(DIA.[Status], 0) As [DiaryStatus],
                   IsNull(DIA.[Highlighted], '') As [Highlighted],
                   IsNull(DIA.[Attachments], '') As [Attachments],
                   Convert(varchar, DIA.[DATE], 106) As [DiaryDate],
                   DIA.[DATE] As [DiaryDDate],
                   RTrim(IsNull(DIA.[FNCode], '')) AS [FNCode],
                   IsNull(DIA.[TeamCode], '') AS [TeamCode],
                   IsNull(DIA.[WorkProcess], 0) AS [WorkProcess],
                   Convert(int, IsNull(DIA.[DelegationStatus], 0)) AS [DelegationStatus],
                   RTrim(IsNull(DIA.[ACTIONCODE], '')) AS [ActionCode],
                   CASE WHEN DIA.[ACTIONTYPE] = 'D' AND DIA.[IMAGENO] > 0
                             THEN 'Length: ' + Convert(varchar, [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DIA.[DATE], DIA.[IMAGENO]), 108)
                        ELSE '' END AS [DURATION],
                   [dbo].[ky_getDuration](DIA.[DYSTARTTIME], DIA.[DYENDTIME], 30) AS [CVDuration],
                   [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DEL.[DATE], DEL.[TIME]) AS [DelegatedDateTime],
                   DEL.[AssignNo]
              FROM [dbo].[ky_WDTaskListSet]
                  (@Handler,
                   @Team,
                   @startdateT,
                   @enddateT,
                   @outstanding,
                   @startno,
                   @count,
                   @Search) TLS
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = TLS.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                   INNER JOIN [dbo].[DiaryDelegations] DEL
                           ON DEL.[AssignNo] = TLS.[AssignNo]) TSK
         INNER JOIN [dbo].[matters] MAT
                    INNER JOIN [dbo].[contacts] CTC
                            ON CTC.[CODE] = MAT.[CLIENTCODE]
                 ON MAT.[Code] = TSK.[CASECODE]
ORDER BY TSK.[DelegatedDateTime]

END
GO

/**************************************************************************************
 *
 * ky_WDTaskListPage2
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListPage2]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @Search            varchar(100))
AS
BEGIN
  SELECT TSK.[CaseCode],
         TSK.[Action],
         TSK.[Subject],
         RTrim(IsNull(CTC.[Name], '')) AS [ClientName],
         RTrim(IsNull(MAT.[Description], '')) AS [MatterDescription],
         Convert(varchar, TSK.[DelegatedDateTime], 106) As [DATE],
         Convert(varchar, TSK.[DelegatedDateTime], 108) As [TIME],
         TSK.[Status],
         TSK.[ActionType],
         TSK.[Priority],
         TSK.[DelegateStatus],
         TSK.[ImageNo],
         TSK.[Handler],
         TSK.[Team],
         TSK.[FromHandler],
         TSK.[ActionID],
         TSK.[DiaryStatus],
         TSK.[Highlighted],
         TSK.[Attachments],
         TSK.[DiaryDate],
         TSK.[DiaryDDate],
         TSK.[FNCode],
         TSK.[TeamCode],
         TSK.[WorkProcess],
         TSK.[DelegationStatus],
         TSK.[ActionCode],
         TSK.[DURATION],
         TSK.[CVDuration],
         TSK.[DELEGATEDDATETIME],
         TSK.[AssignNo]
    FROM (  SELECT RTrim(IsNull(DIA.[CaseCode], '')) As [CaseCode],
                   RTrim([dbo].[ky_removespuriouswhitespace2](substring(DIA.[TEXT1], 1, 500), 500)) As [Action],
                   RTrim([dbo].[ky_removespuriouswhitespace](substring(DIA.[SUBJECT], 1, 150), 150)) As [Subject],
                   DEL.[Status],
                   IsNull(DIA.[ActionType], '') As [ActionType],
                   IsNull(DIA.[Priority], '') As [Priority],
                   IsNull(DEL.[DELEGATESTATUS], 0) As [DelegateStatus],
                   DIA.[ImageNo],
                   RTRIM(IsNull(DEL.[Handler], '')) As [Handler],
                   RTRIM(IsNull(DEL.[Team], '')) As [Team],
                   RTRIM(IsNull(DEL.[FromHandler], '')) As [FromHandler],
                   DIA.[ActionID] As [ActionID],
                   IsNull(DIA.[Status], 0) As [DiaryStatus],
                   IsNull(DIA.[Highlighted], '') As [Highlighted],
                   IsNull(DIA.[Attachments], '') As [Attachments],
                   Convert(varchar, DIA.[DATE], 106) As [DiaryDate],
                   DIA.[DATE] As [DiaryDDate],
                   RTrim(IsNull(DIA.[FNCode], '')) AS [FNCode],
                   IsNull(DIA.[TeamCode], '') AS [TeamCode],
                   IsNull(DIA.[WorkProcess], 0) AS [WorkProcess],
                   Convert(int, IsNull(DIA.[DelegationStatus], 0)) AS [DelegationStatus],
                   RTrim(IsNull(DIA.[ACTIONCODE], '')) AS [ActionCode],
                   CASE WHEN DIA.[ACTIONTYPE] = 'D' AND DIA.[IMAGENO] > 0
                             THEN 'Length: ' + Convert(varchar, [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DIA.[DATE], DIA.[IMAGENO]), 108)
                        ELSE '' END AS [DURATION],
                   [dbo].[ky_getDuration](DIA.[DYSTARTTIME], DIA.[DYENDTIME], 30) AS [CVDuration],
                   [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DEL.[DATE], DEL.[TIME]) AS [DelegatedDateTime],
                   DEL.[AssignNo]
              FROM [dbo].[ky_WDTaskListSet2]
                  (@Handler,
                   @Team,
                   @startdateT,
                   @enddateT,
                   @outstanding,
                   @startno,
                   @count,
                   @Search) TLS
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = TLS.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                   INNER JOIN [dbo].[DiaryDelegations] DEL
                           ON DEL.[AssignNo] = TLS.[AssignNo]) TSK
         INNER JOIN [dbo].[matters] MAT
                    INNER JOIN [dbo].[contacts] CTC
                            ON CTC.[CODE] = MAT.[CLIENTCODE]
                 ON MAT.[Code] = TSK.[CASECODE]
ORDER BY TSK.[DelegatedDateTime]

END
GO

/**************************************************************************************
 *
 * ky_WDTaskListPage2Rev
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListPage2Rev]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @startno           int,
       @count             int,
       @Search            varchar(100))
AS
BEGIN
  SELECT TSK.[CaseCode],
         TSK.[Action],
         TSK.[Subject],
         RTrim(IsNull(CTC.[Name], '')) AS [ClientName],
         RTrim(IsNull(MAT.[Description], '')) AS [MatterDescription],
         Convert(varchar, TSK.[DelegatedDateTime], 106) As [DATE],
         Convert(varchar, TSK.[DelegatedDateTime], 108) As [TIME],
         TSK.[Status],
         TSK.[ActionType],
         TSK.[Priority],
         TSK.[DelegateStatus],
         TSK.[ImageNo],
         TSK.[Handler],
         TSK.[Team],
         TSK.[FromHandler],
         TSK.[ActionID],
         TSK.[DiaryStatus],
         TSK.[Highlighted],
         TSK.[Attachments],
         TSK.[DiaryDate],
         TSK.[DiaryDDate],
         TSK.[FNCode],
         TSK.[TeamCode],
         TSK.[WorkProcess],
         TSK.[DelegationStatus],
         TSK.[ActionCode],
         TSK.[DURATION],
         TSK.[CVDuration],
         TSK.[DELEGATEDDATETIME],
         TSK.[AssignNo]
    FROM (  SELECT RTrim(IsNull(DIA.[CaseCode], '')) As [CaseCode],
                   RTrim([dbo].[ky_removespuriouswhitespace2](substring(DIA.[TEXT1], 1, 500), 500)) As [Action],
                   RTrim([dbo].[ky_removespuriouswhitespace](substring(DIA.[SUBJECT], 1, 150), 150)) As [Subject],
                   DEL.[Status],
                   IsNull(DIA.[ActionType], '') As [ActionType],
                   IsNull(DIA.[Priority], '') As [Priority],
                   IsNull(DEL.[DELEGATESTATUS], 0) As [DelegateStatus],
                   DIA.[ImageNo],
                   RTRIM(IsNull(DEL.[Handler], '')) As [Handler],
                   RTRIM(IsNull(DEL.[Team], '')) As [Team],
                   RTRIM(IsNull(DEL.[FromHandler], '')) As [FromHandler],
                   DIA.[ActionID] As [ActionID],
                   IsNull(DIA.[Status], 0) As [DiaryStatus],
                   IsNull(DIA.[Highlighted], '') As [Highlighted],
                   IsNull(DIA.[Attachments], '') As [Attachments],
                   Convert(varchar, DIA.[DATE], 106) As [DiaryDate],
                   DIA.[DATE] As [DiaryDDate],
                   RTrim(IsNull(DIA.[FNCode], '')) AS [FNCode],
                   IsNull(DIA.[TeamCode], '') AS [TeamCode],
                   IsNull(DIA.[WorkProcess], 0) AS [WorkProcess],
                   Convert(int, IsNull(DIA.[DelegationStatus], 0)) AS [DelegationStatus],
                   RTrim(IsNull(DIA.[ACTIONCODE], '')) AS [ActionCode],
                   CASE WHEN DIA.[ACTIONTYPE] = 'D' AND DIA.[IMAGENO] > 0
                             THEN 'Length: ' + Convert(varchar, [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DIA.[DATE], DIA.[IMAGENO]), 108)
                        ELSE '' END AS [DURATION],
                   [dbo].[ky_getDuration](DIA.[DYSTARTTIME], DIA.[DYENDTIME], 30) AS [CVDuration],
                   [dbo].[ky_ConvertDateAndClarionTimeToDateTime](DEL.[DATE], DEL.[TIME]) AS [DelegatedDateTime],
                   DEL.[AssignNo]
              FROM [dbo].[ky_WDTaskListSet2Rev]
                  (@Handler,
                   @Team,
                   @startdateT,
                   @enddateT,
                   @outstanding,
                   @startno,
                   @count,
                   @Search) TLS
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = TLS.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
                   INNER JOIN [dbo].[DiaryDelegations] DEL
                           ON DEL.[AssignNo] = TLS.[AssignNo]) TSK
         INNER JOIN [dbo].[matters] MAT
                    INNER JOIN [dbo].[contacts] CTC
                            ON CTC.[CODE] = MAT.[CLIENTCODE]
                 ON MAT.[Code] = TSK.[CASECODE]
ORDER BY TSK.[DelegatedDateTime] DESC

END
GO

/**************************************************************************************
 *
 * ky_WDTaskListCountByActionType2
 *
 * Returns the number of items in the Task List to be displayed in 
 * WinDev/WebDev versions of the software filtered by an Action Type
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListCountByActionType2]
      (@Handler           varchar(10),
       @ActionType        varchar(500),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @Search            varchar(100))
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @ActionType = RTrim(IsNull(@ActionType, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@Search <> '%')
  BEGIN
	IF (@outstanding = 0)
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[handler] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
	ELSE
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[handler] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
  END
ELSE
  BEGIN
	IF (@outstanding = 0)
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[handler] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
	ELSE
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[handler] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
  END
  
END
GO

/**************************************************************************************
 *
 * ky_WDTaskListCountByHighlight2
 *
 * Returns the number of highlighted items in the Task List to be displayed in 
 * WinDev/WebDev versions of the software filtered by an Action Type
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListCountByHighlight2]
      (@Handler           varchar(10),
       @ActionType        varchar(500),
       @Highlighted       varchar(1),
       @Priority          varchar(1),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @Search            varchar(100))
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @ActionType = RTrim(IsNull(@ActionType, ''))
SET @Highlighted = RTrim(IsNull(@Highlighted, ''))
SET @Priority = RTrim(IsNull(@Priority, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@Search <> '%')
  BEGIN
	IF (@outstanding = 0)
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[HANDLER] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
	ELSE
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[handler] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
						  AND (   CONVERT(varchar(5000), DIA.[subject]) LIKE @Search
							   OR CONVERT(varchar(5000), DIA.[text1]) LIKE @Search
							   OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
							   OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
							   OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[TEAM] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
  END
ELSE
  BEGIN
	IF (@outstanding = 0)
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[HANDLER] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
	ELSE
	  BEGIN
		IF (@handler = '') AND (@team = '') 
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@handler = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[Team] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE IF (@team = '')
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[handler] = @handler
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
		  END
		ELSE
		  BEGIN
			SELECT COUNT(1) AS TOTAL
			  FROM [dbo].[DiaryDelegations] DEL
				   INNER JOIN [dbo].[Diary] DIA
							  INNER JOIN [dbo].[matters] MS
										 INNER JOIN [dbo].[contacts] CTS
												 ON CTS.[CODE] = MS.[ClientCode]
									  ON MS.[Code] = DIA.[CaseCode]
						   ON DIA.[ActionID] = DEL.[ActionID]
						  AND IsNull(DIA.[HIGHLIGHTED], '') = CASE WHEN @Highlighted = 'Y'
																		THEN 'Y'
																   ELSE IsNull(DIA.[HIGHLIGHTED], '') end 
						  AND DIA.[PRIORITY] = CASE WHEN @Priority = ''
														 THEN DIA.[PRIORITY] 
													ELSE @Priority  end
			 WHERE DEL.[Handler] = @handler
			   AND DEL.[TEAM] = @team
			   AND DEL.[DATE] >= @startdate
			   AND DEL.[DATE] < @enddate
			   AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
			   AND DEL.[Status] = 0
			   AND (   @ActionType LIKE '%*' + DEL.[ACTIONTYPE] + '*%'
					OR @ActionType = '')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, @ActionType) = 0
			END
	  END
  END
END
GO

/**************************************************************************************
 *
 * ky_WDTaskListCount2
 *
 * Returns the Task List to be displayed in WinDev/WebDev versions of the software
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDTaskListCount2]
      (@Handler           varchar(10),
       @Team              varchar(10),
       @startdateT        varchar(8),
       @enddateT          varchar(8),
       @outstanding       int,
       @Search            varchar(100))
AS
BEGIN
DECLARE @startdate datetime
DECLARE @enddate   datetime

SET @handler = RTrim(IsNull(@handler, ''))
SET @Team = RTRIM(IsNull(@team, ''))
SET @startdateT = IsNull(@startdateT, '')
SET @enddateT = IsNull(@enddateT, '')
SET @outstanding = isnull(@outstanding, 1)

IF (IsNull(@startdateT, '') = '')
  BEGIN
    SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
  END
ELSE
  BEGIN
    IF (isdate(@startdateT) = 1)
      BEGIN
        set @startdate = convert(datetime, replace(convert(varchar(10), convert(datetime, @startdateT), 121), '-', ''))
      END
    ELSE
      BEGIN
        SET @startdate = convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', ''))
      END
  END

IF (IsNull(@enddateT, '') = '')
  BEGIN
    SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
  END
ELSE
  BEGIN
    IF (isdate(@enddateT) = 1)
      BEGIN
        set @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), convert(datetime, @enddateT), 121), '-', '')))
      END
    ELSE
      BEGIN
        SET @enddate = dateadd(d, 1, convert(datetime, replace(convert(varchar(10), getDate(), 121), '-', '')))
      END
  END

IF (@Search <> '%')
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@handler = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[Team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@team = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[handler] = @handler
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[Handler] = @handler
               AND DEL.[team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@handler = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[Team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@team = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[handler] = @handler
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
                          AND (   CONVERT(varchar(5000), DIA.[SUBJECT]) LIKE @Search
                               OR CONVERT(varchar(5000), DIA.[Text1]) LIKE @Search
                               OR CONVERT(varchar(20), MS.[Code]) LIKE @Search
                               OR CONVERT(varchar(5000), MS.[Description]) LIKE @Search
                               OR CONVERT(varchar(5000), CTS.[Name]) LIKE @Search)
             WHERE DEL.[Handler] = @handler
               AND DEL.[team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
            END
      END
  END
ELSE
  BEGIN
    IF (@outstanding = 0)
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@handler = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[Team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@team = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[handler] = @handler
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[Handler] = @handler
               AND DEL.[team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
            END
      END
    ELSE
      BEGIN
        IF (@handler = '') AND (@team = '') 
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@handler = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[Team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE IF (@team = '')
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[handler] = @handler
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
          END
        ELSE
          BEGIN
            SELECT COUNT(1) AS TOTAL
              FROM [dbo].[DiaryDelegations] DEL
                   INNER JOIN [dbo].[Diary] DIA
                              INNER JOIN [dbo].[matters] MS
                                         INNER JOIN [dbo].[contacts] CTS
                                                 ON CTS.[CODE] = MS.[ClientCode]
                                      ON MS.[Code] = DIA.[CaseCode]
                           ON DIA.[ActionID] = DEL.[ActionID]
             WHERE DEL.[Handler] = @handler
               AND DEL.[team] = @team
               AND DEL.[DATE] >= @startdate
               AND DEL.[DATE] < @enddate
               AND DEL.[DelType] NOT IN ('Completed', 'Processed', 'Returned')
               AND DEL.[Status] = 0
               --AND [dbo].[ky_NewerAssignments](DEL.[AssignNo], @startdate, @enddate, @Handler, @Team, @outstanding, '') = 0
            END
      END
  END
  
END
GO

/**************************************************************************************
 *
 * ky_WDPushTask3
 *
 * Push a task forward and/or change its text. - Now with support for Time information
 *
 **************************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_WDPushTask3]
      (@AssignNo       int,
       @Date           varchar(17),
       @Duration       int,
       @DueDate        varchar(8),
       @NewText        varchar(4000),
       @NewOtherNotes  varchar(4000),
       @NewSubject     varchar(150),
       @Shuffle        int,
       @Handler        varchar(10),
       @NewActionCode  varchar(30),
       @NewActionTYPE  varchar(10),
       @NewActionSTS   varchar(10),
       @NewActionPTY   varchar(10),
       @NewActionPub   varchar(10),
       @NewActionHigh  varchar(10),
       @NewActionWP    varchar(10),
       @NewActionBLB   varchar(10),
       @NewActionLOC   varchar(50),
       @NewActionHT    varchar(20))
AS
BEGIN       
  DECLARE @Continue int
  DECLARE @ActionID int
  DECLARE @Shuffled int
  DECLARE @ShortDate varchar(8)
  DECLARE @CLARIONTIME int
  DECLARE @CLARIONENDTIME int

  BEGIN TRANSACTION

  SET @Continue = 0
  SET @Shuffled = 0
  SET @ShortDate = LEFT(@Date, 8)
  BEGIN TRY
	  SET @CLARIONTIME = (100 * DATEDIFF(ss, left(@date, 8), @date)) + 1
  END TRY
  BEGIN CATCH
	  SET @CLARIONTIME = 3240001 -- 9 AM
  END CATCH
  
  if (@Duration < 0)
    SET @Duration = 0
    
  SET @CLARIONENDTIME = @CLARIONTIME + (6000 * @Duration)
  
  IF (@CLARIONENDTIME > 8640000)
    SET @CLARIONENDTIME = 8640000
  
  If (RTRIM(ISNULL(@ShortDate, '')) <> '')
    BEGIN
      If (@Continue = 0)
        BEGIN
          BEGIN TRY
		    SELECT @ActionID = DEL.[ActionID],
		           @Shuffled = DateDiff(DD, DEL.[DATE], @ShortDate)
		      FROM [dbo].[DiaryDelegations] DEL
		     WHERE DEL.[AssignNo] = @AssignNo
          END TRY
          BEGIN CATCH
            SET @Continue = 1
          END CATCH
        END
    END
    
  If (@Continue = 0)
    BEGIN
      BEGIN TRY
        UPDATE DEL
           SET DEL.[DATE] = CASE WHEN RTRIM(ISNULL(@ShortDate, '')) = ''
                                         THEN DEL.[DATE]
                                 ELSE CONVERT(datetime, @ShortDate) END,
               DEL.[TIME] = @CLARIONTIME,
               DEL.[DUEDATE] = CASE WHEN RTRIM(ISNULL(@DueDate, '')) = ''
                                         THEN DEL.[DUEDATE]
                                    ELSE CONVERT(datetime, @Duedate) END
          FROM [dbo].[DiaryDelegations] DEL
               INNER JOIN [dbo].[Diary] DIA
                       ON DIA.[ACTIONID] = DEL.[ActionID]
         WHERE DEL.[AssignNo] = @AssignNo
          IF (@@ERROR <> 0)
           SET @Continue = 1

      END TRY
      BEGIN CATCH
        SET @Continue = 1
      END CATCH
    END

  If (@Continue = 0)
    BEGIN
      BEGIN TRY
        UPDATE DIA
           SET DIA.[DATE] = CASE WHEN RTRIM(ISNULL(@Date, '')) = ''
                                         THEN DIA.[DATE]
                                 ELSE CONVERT(datetime, @DATE) END,
               DIA.[DYSTARTTIME] = @CLARIONTIME,
               DIA.[DYENDTIME] = @CLARIONENDTIME,
               DIA.[DUEDATE] = CASE WHEN RTRIM(ISNULL(@DueDate, '')) = ''
                                         THEN DIA.[DUEDATE]
                                    ELSE CONVERT(datetime, @Duedate) END,
               DIA.[TEXT1] = @NewText,
               DIA.[TEXT2] = CASE WHEN @NewOtherNotes = '***ORG***' THEN DIA.[TEXT2] ELSE @NewOtherNotes END,
               DIA.[SUBJECT] = @NewSubject,
               DIA.[ACTIONCODE] = CASE WHEN @NewActionCode = '***ORG***' THEN DIA.[ACTIONCODE] ELSE @NewActionCode END,
               DIA.[ACTIONTYPE] = CASE WHEN @NewActionTYPE = '***ORG***' THEN DIA.[ACTIONTYPE] ELSE @NewActionTYPE END,
               DIA.[ACTIONSTATUS] = CASE WHEN @NewActionSTS = '***ORG***' THEN DIA.[ACTIONSTATUS] ELSE @NewActionSTS END,
               DIA.[PRIORITY] = CASE WHEN @NewActionPTY = '***ORG***' THEN DIA.[PRIORITY] ELSE @NewActionPTY END,
               DIA.[PUBLISH] = CASE WHEN @NewActionPub = '***ORG***' THEN DIA.[PUBLISH] ELSE @NewActionPub END,
               DIA.[HIGHLIGHTED] = CASE WHEN @NewActionHigh = '***ORG***' THEN DIA.[HIGHLIGHTED] ELSE @NewActionHigh END,
               DIA.[WORKPROCESS] = CASE WHEN @NewActionWP = '***ORG***' THEN DIA.[WORKPROCESS] ELSE convert(int, @NewActionWP) END,
               DIA.[BILLABLE] = CASE WHEN @NewActionBLB = '***ORG***' THEN ISNULL(DIA.[BILLABLE], 0) ELSE convert(int, @NewActionBLB) END,
               DIA.[Location] = CASE WHEN @NewActionLOC = '***ORG***' THEN DIA.[Location] ELSE @NewActionLOC END,
               DIA.[HearingType] = CASE WHEN @NewActionHT = '***ORG***' THEN DIA.[HearingType] ELSE @NewActionHT END
          FROM [dbo].[DiaryDelegations] DEL
               INNER JOIN [dbo].[Diary] DIA
                       ON DIA.[ACTIONID] = DEL.[ActionID]
         WHERE DEL.[AssignNo] = @AssignNo

        IF (@@ERROR <> 0)
          SET @Continue = 1
      END TRY
      BEGIN CATCH
        SET @Continue = 1
      END CATCH
    END

  If ((@Continue = 0) AND (@Shuffle = 1) AND (@Shuffled <> 0))
    BEGIN
      BEGIN TRY
        UPDATE DIA
           SET DIA.[DATE] = CASE Convert(varchar(3), DATENAME(WEEKDAY, DATEADD(DD, @Shuffled, DIA.[DATE])))
                                 WHEN 'Sat' THEN DATEADD(DD, @Shuffled + 2, DIA.[DATE])
                                 WHEN 'Sun' THEN DATEADD(DD, @Shuffled + 1, DIA.[DATE])
                                            ELSE DATEADD(DD, @Shuffled, DIA.[DATE]) END,
               DIA.[DUEDATE] = CASE Convert(varchar(3), DATENAME(WEEKDAY, DATEADD(DD, @Shuffled, DIA.[DUEDATE])))
                                    WHEN 'Sat' THEN DATEADD(DD, @Shuffled + 2, DIA.[DUEDATE])
                                    WHEN 'Sun' THEN DATEADD(DD, @Shuffled + 1, DIA.[DUEDATE])
                                               ELSE DATEADD(DD, @Shuffled, DIA.[DUEDATE]) END
          FROM (SELECT DELS.[ActionID] AS [ActionID],
                       DIAS.[CASECODE]
                  FROM [dbo].[DiaryDelegations] DELS
                       INNER JOIN [dbo].[Diary] DIAS
                               ON DIAS.[ACTIONID] = DELS.[ACTIONID] 
                 WHERE DELS.[AssignNo] = @AssignNo
                   AND @Shuffle = 1) SHUF
                INNER JOIN [dbo].[Diary] DIA
                           CROSS APPLY [dbo].[ky_WDActionAuthorisedToComplete](DIA.[ActionID], @Handler) AUT
                        ON DIA.[CASECODE] = SHUF.[CASECODE]
          			   AND DIA.[ACTIONTYPE] IN ('A', 'R')
           			   AND DIA.[STATUS] = 0
           			   AND DIA.[ACTIONID] <> SHUF.[ACTIONID]
           			   AND AUT.[IsAuthorisedToComplete] = 0
      END TRY
      BEGIN CATCH
        SET @Continue = 1
      END CATCH
    END

  If ((@Continue = 0) AND (@Shuffle = 1) AND (@Shuffled <> 0))
    BEGIN
      BEGIN TRY
        UPDATE DEL
           SET DEL.[DATE] = CASE Convert(varchar(3), DATENAME(WEEKDAY, DATEADD(DD, @Shuffled, DEL.[DATE])))
                                 WHEN 'Sat' THEN DATEADD(DD, @Shuffled + 2, DEL.[DATE])
                                 WHEN 'Sun' THEN DATEADD(DD, @Shuffled + 1, DEL.[DATE])
                                            ELSE DATEADD(DD, @Shuffled, DEL.[DATE]) END,
               DEL.[DUEDATE] = CASE Convert(varchar(3), DATENAME(WEEKDAY, DATEADD(DD, @Shuffled, DEL.[DUEDATE])))
                                    WHEN 'Sat' THEN DATEADD(DD, @Shuffled + 2, DEL.[DUEDATE])
                                    WHEN 'Sun' THEN DATEADD(DD, @Shuffled + 1, DEL.[DUEDATE])
                                               ELSE DATEADD(DD, @Shuffled, DEL.[DUEDATE]) END
          FROM (SELECT DELS.[ActionID] AS [ActionID],
                       DIAS.[CASECODE]
                  FROM [dbo].[DiaryDelegations] DELS
                       INNER JOIN [dbo].[Diary] DIAS
                               ON DIAS.[ACTIONID] = DELS.[ACTIONID] 
                 WHERE DELS.[AssignNo] = @AssignNo
                   AND @Shuffle = 1) SHUF
                INNER JOIN [dbo].[Diary] DIA
                           INNER JOIN [dbo].[DiaryDelegations] DEL
                                   ON DEL.[ACTIONID] = DIA.[ACTIONID]
                                  AND DEL.[STATUS] = 0
                           CROSS APPLY [dbo].[ky_WDActionAuthorisedToComplete](DIA.[ActionID], @Handler) AUT
                        ON DIA.[CASECODE] = SHUF.[CASECODE]
           			   AND DIA.[ACTIONTYPE] IN ('A', 'R')
           			   AND DIA.[STATUS] = 0
           			   AND DIA.[ACTIONID] <> SHUF.[ACTIONID]
           			   AND AUT.[IsAuthorisedToComplete] = 0
      END TRY
      BEGIN CATCH
        SET @Continue = 1
      END CATCH
    END
    
  IF (@Continue = 0)
    COMMIT
  ELSE
    ROLLBACK
      
END


GO
