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:
Y
Do
XXX
Y
Do not
YYY
N
Do
ZZZ
*/
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, '', 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