IF NOT EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] [SO]
WHERE [SO].[name] = 'diaryids'
AND [SO].[type] = 'U')
BEGIN
CREATE
TABLE [dbo].[diaryids]
([ID] INT IDENTITY(1, 1) NOT NULL,
CONSTRAINT [PK_diaryids]
PRIMARY KEY
([ID]) ON [PRIMARY])
ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[diaryids] ON
DECLARE @MaxActionID INT
SELECT @MaxActionID = ISNULL(MAX([DIA].[ActionID]), 0)
FROM [dbo].[diary] [DIA]
INSERT
INTO [dbo].[diaryids]
([ID])
SELECT [NW].[NewActionID]
FROM (SELECT @MaxActionID AS [NewActionID]) [NW]
LEFT OUTER JOIN [dbo].[diaryids] [DID]
ON [DID].[ID] = [NW].[NewActionID]
WHERE [DID].[ID] IS NULL
SET IDENTITY_INSERT [dbo].[diaryids] OFF
DELETE [dbo].[diaryids]
GO
DECLARE @MaxActionID INT
SELECT @MaxActionID = ISNULL(MAX([DIA].[ActionID]), 0)
FROM [dbo].[diary] [DIA]
DBCC CHECKIDENT('diaryids', RESEED, @MaxActionID)
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = 'ky_NETGetNextActionID'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETGetNextActionID]
END
GO
CREATE PROCEDURE
[dbo].[ky_NETGetNextActionID]
AS
/**********************************************************************************
*
* [dbo].[ky_NETGetNextActionID]
*
* Dummy Procedure in preparation for the use of the DiarySeed table
*
* Until this is in place, just calculate the next available Action ID
*
* Modification History
* 2015-09-30 Pino Carafa Created
* 2015-11-12 Pino Carafa Include the Diary Deletion Log ActionIDs
* that way, if an action is created and
* then immediately deleted, its actionid is
* not reused.
* 2017-03-09 Pino Carafa Implement diaryids table solution
*
**********************************************************************************/
BEGIN
--Old code START
--DECLARE @nextVAL INT
--DECLARE @delVAL INT
--SELECT @nextVAL = ISNULL(MAX(DIA.[ActionID]), 0) + 1
-- FROM [dbo].[diary] DIA
--SELECT @delVAL = ISNULL(MAX(DIA.[ActionID]), 0) + 1
-- FROM [dbo].[DiaryDeletionLog] DIA
--IF (@delVAL > @nextVAL)
-- BEGIN
-- SET @nextVAL = @delVAL
-- END
--RETURN @nextVAL
--Old code END
DECLARE @MaxActionID INT
DECLARE @bsuccess BIT
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[diaryids] DEFAULT VALUES
SET @MaxActionID = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
-- This while loop handles the possibility that a piece of legacy code uses the OLD mechanism
WHILE EXISTS
(SELECT TOP 1 1
FROM [dbo].[diary] [DIA]
with (nolock)
WHERE [DIA].[ACTIONID] = @MaxActionID)
BEGIN
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[diaryids] DEFAULT VALUES
SET @MaxActionID = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
END
--DELETE [dbo].[diaryids]
RETURN @MaxActionID
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = 'ky_NETGetNextActionIDForClarion'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETGetNextActionIDForClarion]
END
GO
CREATE PROCEDURE
[dbo].[ky_NETGetNextActionIDForClarion]
AS
/**********************************************************************************
*
* [dbo].[ky_NETGetNextActionIDForClarion]
*
* Dummy Procedure in preparation for the use of the DiarySeed table
*
* Until this is in place, just calculate the next available Action ID
*
* Modification History
* 2015-09-30 Pino Carafa Created
* 2015-11-12 Pino Carafa Include the Diary Deletion Log ActionIDs
* that way, if an action is created and
* then immediately deleted, its actionid is
* not reused.
* 2017-03-09 Pino Carafa Implement diaryids table solution
* 2017-03-13 Pino Carafa Created this for Clarion, returning a
* result set
*
**********************************************************************************/
BEGIN
--Old code START
--DECLARE @nextVAL INT
--DECLARE @delVAL INT
--SELECT @nextVAL = ISNULL(MAX(DIA.[ActionID]), 0) + 1
-- FROM [dbo].[diary] DIA
--SELECT @delVAL = ISNULL(MAX(DIA.[ActionID]), 0) + 1
-- FROM [dbo].[DiaryDeletionLog] DIA
--IF (@delVAL > @nextVAL)
-- BEGIN
-- SET @nextVAL = @delVAL
-- END
--RETURN @nextVAL
--Old code END
DECLARE @MaxActionID INT
DECLARE @bsuccess BIT
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[diaryids] DEFAULT VALUES
SET @MaxActionID = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
-- This while loop handles the possibility that a piece of legacy code uses the OLD mechanism
WHILE EXISTS
(SELECT TOP 1 1
FROM [dbo].[diary] [DIA]
with (nolock)
WHERE [DIA].[ACTIONID] = @MaxActionID)
BEGIN
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[diaryids] DEFAULT VALUES
SET @MaxActionID = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
END
--DELETE [dbo].[diaryids]
SELECT @MaxActionID AS [ResultField]
END
GO
IF NOT EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] [SO]
WHERE [SO].[name] = 'trackreferences'
AND [SO].[type] = 'U')
BEGIN
CREATE
TABLE [dbo].[trackreferences]
([ID] INT IDENTITY(1, 1) NOT NULL,
CONSTRAINT [PK_trackreferences]
PRIMARY KEY
([ID]) ON [PRIMARY])
ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[trackreferences] ON
DECLARE @MaxTrackReference INT
SELECT @MaxTrackReference = ISNULL(MAX([DAT].[TrackReference]), 0)
FROM [dbo].[DiaryAttachments] [DAT]
INSERT
INTO [dbo].[trackreferences]
([ID])
SELECT [NW].[NewTrackReference]
FROM (SELECT @MaxTrackReference AS [NewTrackReference]) [NW]
LEFT OUTER JOIN [dbo].[trackreferences] [TR]
ON [TR].[ID] = [NW].[NewTrackReference]
WHERE [TR].[ID] IS NULL
SET IDENTITY_INSERT [dbo].[trackreferences] OFF
DELETE [dbo].[trackreferences]
GO
DECLARE @MaxTrackReference INT
SELECT @MaxTrackReference = ISNULL(MAX([DAT].[TrackReference]), 0)
FROM [dbo].[DiaryAttachments] [DAT]
DBCC CHECKIDENT('trackreferences', RESEED, @MaxTrackReference)
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = 'ky_NETGetNextTrackReference'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETGetNextTrackReference]
END
GO
CREATE PROCEDURE
[dbo].[ky_NETGetNextTrackReference]
AS
/**********************************************************************************
*
* [dbo].[ky_NETGetNextTrackReference]
*
* Dummy Procedure in preparation for the use of the DiaryAttachmentsSeed table
*
* Until this is in place, just calculate the next available TrackReference
*
* Modification History
* 2015-10-01 Pino Carafa Created
* 2015-11-12 Pino Carafa Include the Diary Att Deletion Log Trackrefs
* that way, if an attachment is created and
* then immediately deleted, its trackref is
* not reused.
* 2017-03-09 Pino Carafa Implement trackreferences table solution
*
**********************************************************************************/
BEGIN
--old code START
--DECLARE @nextVAL INT
--DECLARE @delVAL INT
--SELECT @nextVAL = ISNULL(MAX(DAT.[TrackReference]), 0) + 1
-- FROM [dbo].[DiaryAttachments] DAT
--SELECT @delVAL = ISNULL(MAX(DAT.[TrackReference]), 0) + 1
-- FROM [dbo].[DiaryAttachmentDeletionLog] DAT
--IF (@delVAL > @nextVAL)
-- BEGIN
-- SET @nextVAL = @delVAL
-- END
--RETURN @nextVAL
--old code END
DECLARE @MaxTrackReference INT
DECLARE @bsuccess BIT
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[trackreferences] DEFAULT VALUES
SET @MaxTrackReference = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
-- This while loop handles the possibility that a piece of legacy code uses the OLD mechanism
WHILE EXISTS
(SELECT TOP 1 1
FROM [dbo].[DiaryAttachments] [DAT]
with (nolock)
WHERE [DAT].[TrackReference] = @MaxTrackReference)
BEGIN
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[trackreferences] DEFAULT VALUES
SET @MaxTrackReference = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
END
--DELETE [dbo].[trackreferences]
RETURN @MaxTrackReference
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = 'ky_NETGetNextTrackReferenceForClarion'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETGetNextTrackReferenceForClarion]
END
GO
CREATE PROCEDURE
[dbo].[ky_NETGetNextTrackReferenceForClarion]
AS
/**********************************************************************************
*
* [dbo].[ky_NETGetNextTrackReferenceForClarion]
*
* Dummy Procedure in preparation for the use of the DiaryAttachmentsSeed table
*
* Until this is in place, just calculate the next available TrackReference
*
* Modification History
* 2015-10-01 Pino Carafa Created
* 2015-11-12 Pino Carafa Include the Diary Att Deletion Log Trackrefs
* that way, if an attachment is created and
* then immediately deleted, its trackref is
* not reused.
* 2017-03-09 Pino Carafa Implement trackreferences table solution
* 2017-03-13 Pino Carafa Created this for Clarion, returning a
* result set
*
**********************************************************************************/
BEGIN
--old code START
--DECLARE @nextVAL INT
--DECLARE @delVAL INT
--SELECT @nextVAL = ISNULL(MAX(DAT.[TrackReference]), 0) + 1
-- FROM [dbo].[DiaryAttachments] DAT
--SELECT @delVAL = ISNULL(MAX(DAT.[TrackReference]), 0) + 1
-- FROM [dbo].[DiaryAttachmentDeletionLog] DAT
--IF (@delVAL > @nextVAL)
-- BEGIN
-- SET @nextVAL = @delVAL
-- END
--RETURN @nextVAL
--old code END
DECLARE @MaxTrackReference INT
DECLARE @bsuccess BIT
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[trackreferences] DEFAULT VALUES
SET @MaxTrackReference = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
-- This while loop handles the possibility that a piece of legacy code uses the OLD mechanism
WHILE EXISTS
(SELECT TOP 1 1
FROM [dbo].[DiaryAttachments] [DAT]
with (nolock)
WHERE [DAT].[TrackReference] = @MaxTrackReference)
BEGIN
SET @bsuccess = 0
WHILE (@bsuccess = 0)
BEGIN
BEGIN TRY
INSERT
INTO [dbo].[trackreferences] DEFAULT VALUES
SET @MaxTrackReference = SCOPE_IDENTITY()
SET @bsuccess = 1
END TRY
BEGIN CATCH
SET @bsuccess = 0
END CATCH
END
END
--DELETE [dbo].[trackreferences]
SELECT @MaxTrackReference AS [ResultField]
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'spInsertCapturedDocuments'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[spInsertCapturedDocuments]
END
GO
CREATE PROCEDURE
[dbo].[spInsertCapturedDocuments]
(@ActionID BIGINT,
@TrackRefNo INT OUTPUT,
@MatterCode VARCHAR(20),
@HandlerCode VARCHAR(10),
@ActionCode VARCHAR(15),
@Description VARCHAR(MAX),
@DocumentName VARCHAR(100),
@DocumentPath VARCHAR(255),
@DocumentExtn VARCHAR(10),
@DocumentClass VARCHAR(20),
@AssignDate DATETIME,
@FileModifyDate DATETIME,
@FileLastAccessedDate DATETIME,
@SetAsComplete SMALLINT,
@SetDelType VARCHAR(10),
@DueDate DATETIME,
@WorkProcess SMALLINT,
@RETURNACTIONID BIGINT OUTPUT)
--@SetStatus SMALLINT = 0
--@Owner VARCHAR(1) = 'Y',
--@DYSTARTTIME VARCHAR(10), --Time in seconds, confirm with Pino
--@DYENDTIME VARCHAR(10), --Time in seconds, confirm with Pino
AS
/***********************************************************************************************************************
*
* spInsertCapturedDocuments
*
* Inserts Captured Documents into Case
* Modification 2014-06-19 - PINO - added functionality to handle DisplayText column
* use handler CODE rather than NAME for ENTEREDBY and LASTACCESSBY
* Modification 2014-09-18 - PINO - Fixed MAX function - will return NULL on an empty table
*
* Modification 2015-06-19 - MAGESH - Reg KEYD-2362 -Scan Capture give error when trying import doc's
* Added TRY CATCH block to reinsert the missing diary and dairyattachment records.
* Added two output paramter to revert actionid and trackreference number.
*
* Modification 2015-09-30 - PINO - Use ky_NETGetNextActionID
* 07OCT2015 - Sridharan - IF-168 - All Last Week's Tasks
* 09OCT2015 - Sridahran - KEYD-2788 - The Scanned Post Icon is not appearing when documents are scanned in
*
* 2017-03-10 Pino - Use ky_NETGetNextTrackReference
*
***********************************************************************************************************************/
BEGIN
DECLARE @HandlerTeamCode VARCHAR(10) --can be read internally
DECLARE @ActionStatus VARCHAR(10) --= NULL -- read FROM value in selected action code --can be read internally
DECLARE @ActionType CHAR(1) --= 'A' -- read FROM value in selected action code -- need to check IF this category/type --can be read internally
DECLARE @ProcessType CHAR(1) --= 'I' -- read FROM value in selected action code --can be read internally
DECLARE @PriorityText CHAR(20) --= 'Normal' -- read FROM value in selected action code --can be read internally
DECLARE @Publish CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @Priority CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @Highlighted CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @DYSTARTTIME CHAR(10) --Time in seconds, confirm with Pino
DECLARE @DYENDTIME CHAR(10) --Time in seconds, confirm with Pino
DECLARE @NEWTRACKREF INT
DECLARE @COUNT INT
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SET @ActionID = ISNULL(@ActionID, 0)
SET @ActionType = 'A'
SET @ProcessType = 'I'
SET @PriorityText = 'Normal'
SET @Publish = 'N'
SET @Priority = 'N'
SET @Highlighted = 'N'
SET @COUNT =0
SET @RETURNACTIONID = 0
SELECT @HandlerTeamCode = RTRIM(ISNULL(HAN.[Team], ''))
FROM [dbo].[Handlers] HAN
WHERE HAN.[CODE] = @HandlerCode
SELECT @ActionStatus = RTRIM(ISNULL(TA.[Status], '')),
@ActionType = RTRIM(ISNULL(TA.[ACTIONCATEGORY], '')),
@ProcessType = RTRIM(ISNULL(TA.[PROCESSTYPE], '')),
@Publish = CASE WHEN RTRIM(ISNULL(TA.[PUBLISHER], '')) IN ('N', 'P', 'E', 'A')
THEN RTRIM(ISNULL(TA.[PUBLISHER], ''))
ELSE 'N' END,
@PriorityText = CASE WHEN RTRIM(ISNULL(TA.[PRIORITY], 'Normal')) IN ('Low', 'Normal', 'High')
THEN RTRIM(ISNULL(TA.[PRIORITY], 'Normal'))
ELSE 'Normal' END,
@Highlighted = CASE WHEN RTRIM(ISNULL(TA.[HIGHLIGHTED], 'N')) = 'Y'
THEN 'Y'
ELSE 'N' END
FROM [dbo].[TemplateActions] TA
WHERE TA.[ACTIONCODE] = @ActionCode
SET @Priority = CONVERT(VARCHAR(1), @PriorityText)
SET @DYSTARTTIME = dbo.ky_ConvertTimeToClarion(@AssignDate)
SET @DYENDTIME = @DYSTARTTIME
--PRINT @HandlerName + ' @HandlerName'
--PRINT @HandlerTeamCode + ' @HandlerTeamCode'
--PRINT @ActionStatus + ' @ActionStatus'
--PRINT @ActionType + ' @ActionType'
--PRINT @ProcessType + ' @ProcessType'
--PRINT @Publish + ' @Publish'
--PRINT @Priority + ' @Priority'
--PRINT @Highlighted + ' @Highlighted'
--PRINT @DYSTARTTIME + ' @DYSTARTTIME'
----SELECT FROM WHERE
IF (@ActionID = 0)
BEGIN
RETURN_ERROR1:
BEGIN TRY
-- PINO 2015-09-30 Start
EXEC @ActionID = [dbo].[ky_NETGetNextActionID]
--SELECT @ActionID = ISNULL(MAX(ActionID), 0) +1
-- FROM [dbo].[diary]
-- PINO 2015-09-30 End
--SET @ActionID = ISNULL(@ActionID, 1)
INSERT
INTO [dbo].[diary]
([CASECODE],
[DATE],
[STATUS],
[ACTIONCODE],
[ACTIONSTATUS],
[ACTIONTYPE],
[PROCESSTYPE],
[FNCODE],
[TEAMCODE],
[TEXT1],
[DisplayText],
[DUEDATE],
[IMAGENO],
[PUBLISH],
[DYSTARTTIME],
[DYENDTIME],
[ACTIONID],
[ORGINALACTIONID],
[PRIORITY],
[HIGHLIGHTED],
[MILESTEONETYPE],
[ATTACHMENTS],
[WORKPROCESS],
[BILLABLE],
[DELEGATIONSTATUS],
[TXMSENT])
VALUES(@MatterCode,
@AssignDate,
@SetAsComplete,
@ActionCode,
@ActionStatus,
@ActionType,
@ProcessType,
@HandlerCode,
@HandlerTeamCode,
@Description,
[dbo].[ky_RemoveSpuriousWhitespace2](ISNULL(CONVERT(VARCHAR(MAX), @Description), ''), 200),
@DueDate,
0,
@Publish,
@DYSTARTTIME,
@DYENDTIME,
@ActionID,
@ActionID,
@Priority,
@Highlighted,
@HandlerCode,
'',
@WorkProcess,
0,
0,
0)
IF(@SetAsComplete = 1)
BEGIN
INSERT
INTO [dbo].[DiaryDelegations]
([ACTIONID],
[HANDLER],
[TEAM],
[DATE],
[TIME],
[DATER],
[TIMER],
[DUEDATE],
[DUETIME],
[STATUS], -- need to check
[OWNER], -- need to check
[DELEGATE],-- need to check
[DELEGATESTATUS], -- need to check
[ACTIONTYPE],
[FROMHANDLER],
[ReturnedBy],
[DELTYPE])
VALUES(@ActionID,
@HandlerCode,
@HandlerTeamCode,
@AssignDate,
@DYSTARTTIME,
@AssignDate,
@DYSTARTTIME,
@AssignDate,
@DYENDTIME,
@SetAsComplete, -- need to check
'Y',
@HandlerCode,
0, -- need to check
@ActionType,
@HandlerCode,
@HandlerCode ,
'Created')
END
INSERT
INTO [dbo].[DiaryDelegations]
([ACTIONID],
[HANDLER],
[TEAM],
[DATE],
[TIME],
[DUEDATE],
[DUETIME],
[STATUS], -- need to check
[OWNER], -- need to check
[DELEGATE],-- need to check
[DELEGATESTATUS], -- need to check
[ACTIONTYPE],
[FROMHANDLER],
[DELTYPE])
VALUES(@ActionID,
@HandlerCode,
@HandlerTeamCode,
@AssignDate,
@DYSTARTTIME,
@AssignDate,
@DYENDTIME,
@SetAsComplete, -- need to check
'Y',
@HandlerCode,
1, -- need to check
@ActionType,
@HandlerCode,
@SetDelType)
--SET @RETURNACTIONID = (SELECT @ActionID)
END TRY
BEGIN CATCH
IF @COUNT<4
BEGIN
SET @COUNT =@COUNT +1
GOTO RETURN_ERROR1
END
ELSE
BEGIN
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)
END
END CATCH
END
SET @COUNT =0
RETURN_ERROR2:
BEGIN TRY
--SELECT
-- @NEWTRACKREF = MAX(ISNULL(TrackReference,0))+1
--FROM
-- DiaryAttachments
EXEC @NEWTRACKREF = [dbo].[ky_NETGetNextTrackReference]
SET @TrackRefNo = (SELECT @NEWTRACKREF )
SET @DocumentPath = (SELECT REPLACE(@DocumentPath,'___NTR___',CAST(@NEWTRACKREF AS NVARCHAR)))
INSERT
INTO [dbo].[DiaryAttachments]
([DIARYID],
[TRACKREFERENCE],
[CASECODE],
[NAME],
[DOCUMENT],
[FILEPATH],
[TYPE],
[DOCCLASS],
[DATEENTERED],
[ENTEREDBY],
[LASTACCESSDATE],
[LASTACCESSBY],
[SYSTEM],
[DICTATIONFILE],
[SOURCE])
VALUES(@ActionID,
@TrackRefNo,
@MatterCode,
'',
@DocumentName,
@DocumentPath,
@DocumentExtn,
@DocumentClass,
@AssignDate,
@HandlerCode,
@FileLastAccessedDate,
@HandlerCode,
'N',
'N',
'Captured')
END TRY
BEGIN CATCH
IF @COUNT<4
BEGIN
SET @COUNT =@COUNT +1
GOTO RETURN_ERROR2
END
ELSE
BEGIN
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)
END
END CATCH
SET @RETURNACTIONID = (SELECT @ActionID)
--Return ActionID for a new INSERT INTO diary
--SELECT CAST(@ActionID AS NVARCHAR) +'***'+ CAST(@NEWTRACKREF AS NVARCHAR)
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_NetSPInsertDocumentsFromCSV'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NetSPInsertDocumentsFromCSV]
END
GO
CREATE PROCEDURE
[dbo].[ky_NetSPInsertDocumentsFromCSV]
(@ActionID INT,
@MatterCode VARCHAR(20),
@HandlerCode VARCHAR(10),
@ActionCode VARCHAR(15),
@Description VARCHAR(MAX),
@DocumentName VARCHAR(100),
@DocumentPath VARCHAR(255),
@DocumentExtn VARCHAR(10),
@DocumentClass VARCHAR(40),
@AssignDate DATETIME,
@FileModifyDate DATETIME,
@FileLastAccessedDate DATETIME,
@SetAsComplete SMALLINT,
@SetDelType VARCHAR(10),
@DueDate DATETIME,
@WorkProcess SMALLINT,
@Author VARCHAR(MAX))
AS
/*********************************************************************************************************************************************************************
* [dbo].[ky_NetSPInsertDocumentsFromCSV]
*
* Created 2014-06-24 Sridharan
*
* Modified 2014-09-18 Pino - use ISNULL(MAX as MAX will return NULL on an empty table
* 2014-10-22 Pino - Changes to handle IManage and file locations
* Increase handler / team codes to 10 characters in preparation for future extensions
* Auto Generate Track Reference and Document Path location
*
* This Procedure has been checked for SQL statement general standards/optimization on 24/11/2014 by Praveen Yadav.P
*
* Modified 2015-09-30 Pino - ky_NETGetNextActionID
* Modified 2015-03-10 Pino - ky_NETGetNextTrackReference
*
*********************************************************************************************************************************************************************/
BEGIN
DECLARE @HandlerName VARCHAR(8) -- can be read internally
DECLARE @HandlerTeamCode VARCHAR(10) -- can be read internally
DECLARE @ActionStatus CHAR(3) --= NULL -- read FROM value in selected action code --can be read internally
DECLARE @ActionType CHAR(1) --= 'A' -- read FROM value in selected action code -- need to check IF this category/type --can be read internally
DECLARE @ProcessType CHAR(1) --= 'I' -- read FROM value in selected action code --can be read internally
DECLARE @PriorityText CHAR(20) --= 'Normal' -- read FROM value in selected action code --can be read internally
DECLARE @Publish CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @Priority CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @Highlighted CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @DYSTARTTIME CHAR(10) --Time in seconds, confirm with Pino
DECLARE @DYENDTIME CHAR(10) --Time in seconds, confirm with Pino
DECLARE @DocClass CHAR(3)
DECLARE @TrackRef INT
SET @ActionID = ISNULL(@ActionID, 0)
SET @ActionType = 'A'
SET @ProcessType = 'I'
SET @PriorityText = 'Normal'
SET @Publish = 'N'
SET @Priority = 'N'
SET @Highlighted = 'N'
IF EXISTS(SELECT 1
FROM [dbo].[DocumentClasses] DCL
WHERE DCL.[CLASSDESCRIPTION] = @DocumentClass
AND DCL.[Retired] = 'N'
)
BEGIN
SELECT
@DocClass = DCL.[CLASSCODE]
FROM
[DocumentClasses] DCL
WHERE
DCL.[CLASSDESCRIPTION] = @DocumentClass
AND
DCL.[Retired] = 'N'
END
ELSE
SET @DocClass = 'GEN'
SELECT @HandlerName = HAN.[Name],
@HandlerTeamCode = HAN.[Team]
FROM
[Handlers] HAN
WHERE
HAN.[CODE] = @HandlerCode
SELECT @ActionStatus = TAC.[Status],
@ActionType = TAC.[ACTTYPE],
@ProcessType = TAC.[PROCESSTYPE],
@Publish = TAC.[PUBLISHER],
@PriorityText = TAC.[PRIORITY],
@Highlighted = TAC.[HIGHLIGHTED]
FROM
[TemplateActions] TAC
WHERE
TAC.[ACTIONCODE]=@ActionCode
SET @Priority = CONVERT(VARCHAR(1), ISNULL(@PriorityText, 'Normal'))
SET @Priority = CASE WHEN ISNULL(@Priority, '') IN ('L', 'N', 'H')
THEN UPPER(@Priority)
ELSE 'N' END
SET @DYSTARTTIME = [dbo].[ky_ConvertTimeToClarion](@AssignDate)
SET @DYENDTIME = @DYSTARTTIME
IF (@ActionID = 0)
BEGIN
-- Pino 2015-09-30 Start
--SELECT
-- @ActionID = ISNULL(MAX(DIA.[ActionID]),0)+ 1
--FROM
-- [Diary] DIA
EXEC @ActionID = [dbo].[ky_NETGetNextActionID]
-- Pino 2015-09-30 End
INSERT
INTO [dbo].[Diary]
(
[CASECODE],
[DATE],
[STATUS],
[ACTIONCODE],
[ACTIONSTATUS],
[ACTIONTYPE],
[PROCESSTYPE],
[FNCODE],
[TEAMCODE],
[TEXT1],
[DUEDATE],
[IMAGENO],
[PUBLISH],
[DYSTARTTIME],
[DYENDTIME],
[ACTIONID],
[ORGINALACTIONID],
[PRIORITY],
[HIGHLIGHTED],
[MILESTEONETYPE],
[ATTACHMENTS],
[WORKPROCESS],
[BILLABLE],
[DELEGATIONSTATUS],
[TXMSENT],
[TEXT2],
[SUBJECT],
[DisplayText]
)
VALUES(
@MatterCode,
@AssignDate,
@SetAsComplete,
@ActionCode,
@ActionStatus,
@ActionType,
@ProcessType,
@HandlerCode,
@HandlerTeamCode,
@Description,
@DueDate,
0,
@Publish,
@DYSTARTTIME,
@DYENDTIME,
@ActionID,
@ActionID,
@Priority,
@Highlighted,
@HandlerCode,
'',
@WorkProcess,
0,
0,
0,
@Author,
@Description,
[dbo].[ky_RemoveSpuriousWhitespace2](ISNULL(CONVERT(VARCHAR(MAX), @Description), ''), 200)
)
INSERT
INTO [dbo].[DiaryDelegations]
(
[ACTIONID],
[HANDLER],
[TEAM],
[DATE],
[TIME],
[DUEDATE],
[DUETIME],
[STATUS], -- need to check
[OWNER], -- need to check
[DELEGATE], -- need to check
[DELEGATESTATUS], -- need to check
[ACTIONTYPE],
[FROMHANDLER],
[DELTYPE]
)
VALUES(
@ActionID,
@HandlerCode,
@HandlerTeamCode,
@AssignDate,
@DYSTARTTIME,
@AssignDate,
@DYENDTIME,
@SetAsComplete, -- need to check
'Y',
@HandlerCode,
0, -- need to check
@ActionType,
@HandlerCode,
@SetDelType
)
END
EXEC @TrackRef = [dbo].[ky_NETGetNextTrackReference]
--SELECT
-- @TrackRef = ISNULL(MAX(DAT.[TrackReference]),0)+ 1
-- FROM
-- [DiaryAttachments] DAT
SET @DocumentPath = REPLACE(@DocumentPath, '{NewTrackRef}', CONVERT(VARCHAR(10), @TrackRef))
SET @DocumentPath = REPLACE(@DocumentPath, '{DocumentName}', CONVERT(VARCHAR(10), @DocumentName))
INSERT
INTO [DiaryAttachments]
(
[DIARYID],
[TRACKREFERENCE],
[CASECODE],
[NAME],
[DOCUMENT],
[FILEPATH],
[TYPE],
[DOCCLASS],
[DATEENTERED],
[ENTEREDBY],
[LASTACCESSDATE],
[LASTACCESSBY],
[SYSTEM],
[DICTATIONFILE],
[SOURCE]
)
VALUES (
@ActionID,
@TrackRef,
@MatterCode,
'',
@DocumentName,
@DocumentPath + '.PDF',
@DocumentExtn,
@DocClass,
@AssignDate,
@HandlerCode,
@FileLastAccessedDate,
@HandlerCode,
'N',
'N',
'Captured'
)
--Return ActionID for a new INSERT INTO diary
SELECT @ActionID AS [ActionID],
@TrackRef As [TrackReference]
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_NetSPInsertDocumentsFromNuix'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NetSPInsertDocumentsFromNuix]
END
GO
CREATE PROCEDURE
[dbo].[ky_NetSPInsertDocumentsFromNuix]
(@ActionID INT,
@MatterCode VARCHAR(20),
@HandlerCode VARCHAR(10),
@ActionCode VARCHAR(15),
@Description VARCHAR(MAX),
@DocumentPath VARCHAR(255),
@DocumentExtn VARCHAR(10),
@DocumentClass VARCHAR(40),
@AssignDate DATETIME,
@FileModifyDate DATETIME,
@FileLastAccessedDate DATETIME,
@SetAsComplete SMALLINT,
@SetDelType VARCHAR(10),
@DueDate DATETIME,
@WorkProcess SMALLINT,
@Author VARCHAR(MAX),
@EmailAddress VARCHAR(150),
@AddressTo VARCHAR(200),
@AddressCC VARCHAR(200),
@Text2 VARCHAR(MAX))
AS
SET NOCOUNT ON
/*********************************************************************************************************************************************************************
*
* [dbo].[ky_NetSPInsertDocumentsFromNuix]
*
* Created 31JULY2015 Sridharan
*
* 30SEPT2015 Pino Changed parameters to match data types
* Use ky_NETGetNextActionID
* 10MARC2017 Pino Use ky_NETGetNextTrackReference
*
*********************************************************************************************************************************************************************/
BEGIN
DECLARE @HandlerTeamCode VARCHAR(10) -- can be read internally
DECLARE @ActionStatus CHAR(3) --= NULL -- read FROM value in selected action code --can be read internally
DECLARE @ProcessType CHAR(1) --= 'I' -- read FROM value in selected action code --can be read internally
DECLARE @ActionType CHAR(1)
DECLARE @PriorityText CHAR(20) --= 'Normal' -- read FROM value in selected action code --can be read internally
DECLARE @Publish CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @Priority CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @Highlighted CHAR(1) --= 'N' -- read FROM value in selected action code --can be read internally
DECLARE @DYSTARTTIME CHAR(10) --Time in seconds, confirm with Pino
DECLARE @DYENDTIME CHAR(10) --Time in seconds, confirm with Pino
DECLARE @DocClass CHAR(3)
DECLARE @Flag INT
DECLARE @TrackRef INT
SET @ActionID = ISNULL(@ActionID, 0)
SET @ProcessType = 'I'
SET @PriorityText = 'Normal'
SET @Publish = 'N'
SET @Priority = 'N'
SET @Highlighted = 'N'
IF EXISTS(SELECT 1
FROM [dbo].[DocumentClasses] DCL
WHERE DCL.[CLASSDESCRIPTION] like @DocumentClass + '%'
AND DCL.[Retired] = 'N')
BEGIN
SELECT @DocClass = DCL.[CLASSCODE]
FROM [dbo].[DocumentClasses] DCL
WHERE DCL.[CLASSDESCRIPTION] like @DocumentClass + '%'
AND DCL.[Retired] = 'N'
END
ELSE
SET @DocClass = 'GEN'
SELECT
@HandlerTeamCode = HAN.[Team]
FROM
[dbo].[Handlers] HAN
WHERE
HAN.[CODE] = @HandlerCode
SELECT
@ActionStatus = TAC.[Status],
@ActionType = TAC.[ACTTYPE],
@ProcessType = TAC.[PROCESSTYPE],
@Publish = TAC.[PUBLISHER],
@PriorityText = TAC.[PRIORITY],
@Highlighted = TAC.[HIGHLIGHTED],
@Flag = TAC.Flag
FROM
[dbo].[TemplateActions] TAC
WHERE
TAC.[ACTIONCODE]=@ActionCode
IF(@DocClass = 'MSG')
BEGIN
SET @ActionType = 'E'
END
SET @Priority = CONVERT(VARCHAR(1), ISNULL(@PriorityText, 'Normal'))
SET @Priority = CASE WHEN ISNULL(@Priority, '') IN ('L', 'N', 'H')
THEN UPPER(@Priority)
ELSE 'N' END
SET @DYSTARTTIME = [dbo].[ky_ConvertTimeToClarion](@AssignDate)
SET @DYENDTIME = @DYSTARTTIME
IF (@ActionID = 0)
BEGIN
-- Pino 2015-09-30 start
EXEC @ActionID = [dbo].[ky_NETGetNextActionID]
--SELECT @ActionID = ISNULL(MAX(DIA.[ActionID]),0)+ 1
-- FROM [dbo].[diary] DIA
-- Pino 2015-09-30 End
INSERT
INTO [dbo].[diary]
([CASECODE],
[DATE],
[STATUS],
[ACTIONCODE],
[ACTIONSTATUS],
[ACTIONTYPE],
[PROCESSTYPE],
[FNCODE],
[TEAMCODE],
[TEXT1],
[TEXT2],
[DUEDATE],
[IMAGENO],
[PUBLISH],
[DYSTARTTIME],
[DYENDTIME],
[ACTIONID],
[ORGINALACTIONID],
[PRIORITY],
[HIGHLIGHTED],
[MILESTEONETYPE],
[ATTACHMENTS],
[WORKPROCESS],
[BILLABLE],
[EMAILADDRESS],
[DELEGATIONSTATUS],
[TXMSENT],
[SUBJECT],
[DisplayText],
[Flag],
[ADDRESSTO],
[CCTo] )
VALUES(@MatterCode,
@AssignDate,
@SetAsComplete,
@ActionCode,
@ActionStatus,
@ActionType,
@ProcessType,
@HandlerCode,
@HandlerTeamCode,
@Description,
@Text2,
@DueDate,
0,
@Publish,
@DYSTARTTIME,
@DYENDTIME,
@ActionID,
@ActionID,
@Priority,
@Highlighted,
@HandlerCode,
'',
@WorkProcess,
0,
@EmailAddress,
0,
0,
@Description,
[dbo].[ky_RemoveSpuriousWhitespace2](ISNULL(CONVERT(VARCHAR(MAX), @Description), ''), 200),
@Flag,
@AddressTo,
@AddressCC)
INSERT
INTO [dbo].[DiaryDelegations]
([ACTIONID],
[HANDLER],
[TEAM],
[DATE],
[TIME],
[DUEDATE],
[DUETIME],
[STATUS], -- need to check
[OWNER], -- need to check
[DELEGATE], -- need to check
[DELEGATESTATUS], -- need to check
[ACTIONTYPE],
[FROMHANDLER],
[DELTYPE])
VALUES(@ActionID,
@HandlerCode,
@HandlerTeamCode,
@AssignDate,
@DYSTARTTIME,
@AssignDate,
@DYENDTIME,
@SetAsComplete, -- need to check
'Y',
@HandlerCode,
0, -- need to check
@ActionType,
@HandlerCode,
@SetDelType)
END
EXEC @TrackRef = [dbo].[ky_NETGetNextTrackReference]
--SELECT
-- @TrackRef = ISNULL(MAX(DAT.[TrackReference]),0)+ 1
--FROM
-- [dbo].[DiaryAttachments] DAT
SET @DocumentPath = REPLACE(@DocumentPath, '{NewTrackRef}', CONVERT(VARCHAR(10), @TrackRef))
SET @DocumentPath = REPLACE(@DocumentPath, '{DocumentName}', CONVERT(VARCHAR(100), @Description))
INSERT
INTO [dbo].[DiaryAttachments]
([DIARYID],
[TRACKREFERENCE],
[CASECODE],
[NAME],
[DOCUMENT],
[FILEPATH],
[TYPE],
[DOCCLASS],
[DATEENTERED],
[ENTEREDBY],
[LASTACCESSDATE],
[LASTACCESSBY],
[SYSTEM],
[DICTATIONFILE],
[SOURCE])
VALUES(@ActionID,
@TrackRef,
@MatterCode,
'',
@Description,
@DocumentPath + '.PDF',
@DocumentExtn,
@DocClass,
@AssignDate,
@HandlerCode,
@FileLastAccessedDate,
@HandlerCode,
'N',
'N',
'Captured')
--Return ActionID for a new INSERT INTO diary
SELECT
@ActionID AS [ActionID],
@TrackRef As [TrackReference]
END
SET NOCOUNT OFF
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_NETSPCreateDiaryAttachment'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETSPCreateDiaryAttachment]
END
GO
CREATE PROCEDURE [dbo].[ky_NETSPCreateDiaryAttachment]
(@pDiaryID Int=0,
@pName VARCHAR(10),
@pDocName VARCHAR(100),
@pDocument VARCHAR(255),
@pType VARCHAR(10),
@pDocClass VARCHAR(10),
@pDateEntered VARCHAR(17),
@pEnterBy VARCHAR(8),
@pLastAccessed VARCHAR(17),
@pLastAccessedby VARCHAR(8),
@pSystem VARCHAR(1),
@pDictationFile VARCHAR(255),
@pSource VARCHAR(20),
@pDocFolders VARCHAR(MAX))
AS
/***************************************************************************************************
*
* ky_NETSPCreateDiaryAttachment
*
* Create a Diary Attachment for a particular Action
*
* Modification History
* 2015-09-16 Pino Carafa Imported from Release 1
* 2015-10-19 Sridharan KEYD-2789-Copy to Another File doesn't work correctly
* when using the Document Manager
* 2016-05-01 Pino Carafa Add DocFolders parameter
* Class can now be up to 10 characters
* @strType can now be up to 10 characters
* 2016-10-24 Pino Carafa Remove invalid filename characters in filename
* 2016-10-27 Pino Carafa Handle full file paths through @pDocument
* 2017-03-10 Pino Carafa Use ky_NETGetNextTrackReference
*
***************************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @strcasecode VARCHAR(20)
DECLARE @inTrackRef Int
DECLARE @strFileName VARCHAR(255)
DECLARE @strUNCFileName VARCHAR(500)
DECLARE @strMatterNo VARCHAR(10)
DECLARE @strClientCode VARCHAR(10)
DECLARE @strDefaultFolder VARCHAR(255)
DECLARE @strSource VARCHAR(20)
DECLARE @strType VARCHAR(10)
DECLARE @continue INT
DECLARE @error VARCHAR(1000)
SET @continue = 0
SET @error = ''
SET @pDiaryID = ISNULL(@pDiaryID, 0)
IF (@pDiaryID = 0)
BEGIN
SET @continue = 12
SET @error = 'invalid or missing Diary ID'
END
-- Get Diary and Matter information
IF (@continue = 0)
BEGIN
BEGIN TRY
SELECT @strcasecode = RTRIM(ISNULL(MAT.[code], '')),
@strClientCode = RTRIM(ISNULL(MAT.[clientcode], '')),
@strMatterNo = RTRIM(ISNULL(MAT.[matter], '')),
@strSource = RTRIM(ISNULL(DIA.[PROCESSTYPE], ''))
FROM [dbo].[diary] DIA
INNER JOIN [dbo].[matters] MAT
ON MAT.[code] = DIA.[CASECODE]
WHERE DIA.[ACTIONID] = @pDiaryID
SET @strcasecode = ISNULL(@strcasecode, '')
SET @strClientCode = ISNULL(@strClientCode, '')
SET @strMatterNo = ISNULL(@strMatterNo, '')
SET @strSource = CASE WHEN ISNULL(@pSource, '') = ''
THEN CASE WHEN ISNULL(@strSource, 'I') = 'I'
THEN 'Received E-mail'
ELSE 'Sent E-mail' END
ELSE @pSource END
IF (@strcasecode = '')
BEGIN
SET @continue = 2
SET @error = 'invalid or missing Diary ID'
END
END TRY
BEGIN CATCH
SET @continue = 1
SET @error = 'Error retrieving Diary information for Diary ID: ' + ISNULL(convert(VARCHAR(10), @pDiaryID), 'unknown') + ' ' + Error_Message()
END CATCH
END
-- Get Next TrackReference -----------------------------------------------------------------------------------------------------------------------------------------
IF (@continue = 0)
BEGIN
BEGIN TRY
--SELECT @inTrackRef = ISNULL(MAX(DAT.[TrackReference]), 0) + 1
-- FROM [dbo].[DiaryAttachments] DAT
EXEC @inTrackRef = [dbo].[ky_NETGetNextTrackReference]
END TRY
BEGIN CATCH
SET @continue = 3
SET @error = 'Error calculating next Track Reference: ' + Error_Message()
END CATCH
END
-- Write DiaryAttachment Record -----------------------------------------------------------------------------------------------------------------------------------------
IF (@continue = 0)
BEGIN
BEGIN TRY
SELECT @strDefaultFolder = (SELECT RTRIM(ISNULL(SC.[SCWPFOLDER], ''))
FROM [dbo].[SystemConfig] SC)
SET @strDefaultFolder = ISNULL(@strDefaultFolder, '')
IF (@strDefaultFolder = '')
BEGIN
SET @continue = 4
SET @error = 'SCWPFOLDER not SET in System Config'
END
END TRY
BEGIN CATCH
SET @continue = 5
SET @error = 'Error retrieving SCWPFOLDER: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
BEGIN TRY
SELECT @strFileName = RTRIM(ISNULL(CLT.[DCOUMENTFOLDER], ''))
FROM [dbo].[client] CLT
WHERE CLT.[CLCODE] = @strClientCode
SET @strFileName = RTRIM(ISNULL(@strFileName, ''))
IF (@strFileName = '')
BEGIN
SET @strFileName = @strDefaultFolder + '\' + @strClientCode
UPDATE CLT
SET CLT.[DCOUMENTFOLDER] = @strFileName
FROM [dbo].[client] CLT
WHERE CLT.[CLCODE] = @strClientCode
END
END TRY
BEGIN CATCH
SET @continue = 6
SET @error = 'Error establishing Document Folder: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
SET @pDocument = RTRIM(ISNULL(@pDocument, ''))
SET @pDocument = REPLACE(@pDocument, CHAR(13) + CHAR(10), '_')
IF (@pDocument = '')
BEGIN
SET @continue = 7
SET @error = 'missing document name'
END
END
IF (@continue = 0)
BEGIN
SET @pDocName = RTRIM(ISNULL(@pDocName, ''))
SET @pDocName = REPLACE(@pDocName, CHAR(13) + CHAR(10), '_')
IF (@pDocName = '')
BEGIN
SET @continue = 7
SET @error = 'missing document name'
END
END
IF (@continue = 0)
BEGIN
BEGIN TRY
SET @pType = RTRIM(ISNULL(@pType, ''))
SET @strType = convert(VARCHAR(10), @pType)
END TRY
BEGIN CATCH
SET @continue = 8
SET @error = 'Error interpreting Document Type: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
BEGIN TRY
DECLARE @pDocName2 VARCHAR(100)
DECLARE @pDocumentWork VARCHAR(255)
SET @pDocumentWork = REVERSE(@pDocument)
IF CHARINDEX('\', @pDocumentWork) > 0
BEGIN
SET @pDocumentWork = SUBSTRING(@pDocumentWork, 1, CHARINDEX('\', @pDocumentWork) - 1)
END
IF CHARINDEX('.', @pDocumentWork) > 0
BEGIN
SET @pDocumentWork = SUBSTRING(@pDocumentWork, CHARINDEX('.', @pDocumentWork) + 1, LEN(@pDocumentWork) - CHARINDEX('.', @pDocumentWork))
END
SET @pDocumentWork = REVERSE(@pDocumentWork)
SET @pDocName2 = REPLACE(REPLACE(REPLACE([dbo].[ky_NETFNRemovePatternFromString](@pDocumentWork, '%[:*<>$&.!?@#~/\\\%|`¬{}\+=_^€£",]%'), CHAR(13), ''), CHAR(10), ''), CHAR(9), '')
SET @strFileName = @strFileName
+ '\'
+ @strMatterNo
+ '\'
+ @pDocName2
+ '_'
+ convert(VARCHAR(10), @inTrackRef)
IF (@pType <> '')
BEGIN
SET @strFileName = @strFileName
+ '.'
+ @pType
END
END TRY
BEGIN CATCH
SET @continue = 9
SET @error = 'Error establishing filename: ' + Error_Message()
END CATCH
END
SET @strFileName = ISNULL(@strFileName, '')
SET @strUNCFileName = @strFileName
IF (SUBSTRING(@strUNCFileName, 2, 1) = ':')
BEGIN
SELECT @strUNCFileName = ISNULL(MAX(UNC.[UNC]) + SUBSTRING(@strUNCFileName, 3, LEN(@strUNCFileName) - 2), @strUNCFileName)
FROM [dbo].[UNCAlias] UNC
WHERE SUBSTRING(@strUNCFileName, 1, 1) = UNC.[Drive]
SET @strUNCFileName = LEFT(@strUNCFileName, 2) + REPLACE(RIGHT(@strUNCFileName, LEN(@strUNCFileName) - 2), '\\', '\')
END
IF(@strType = 'FOL')
BEGIN
SET @strFileName = @pDocument
END
IF (@continue = 0)
BEGIN
BEGIN TRY
INSERT INTO DiaryAttachments
([DiaryId],
[TrackReference],
[CaseCode],
[Name],
[Document],
[Filepath],
[Type],
[DocClass],
[DateEntered],
[EnteredBy],
[LastAccessDate],
[LastAccessBy],
[System],
[Dictationfile],
[Source])
VALUES(@pDiaryID,
@inTrackRef,
@strcasecode,
@pName,
@pDocName,
@strFileName,
@strType,
@pDocClass,
convert(DATETIME, @pDateEntered),
@pEnterBy,
convert(DATETIME, @pLastAccessed),
@pLastAccessedby,
@pSystem,
@pDictationFile,
@strSource)
END TRY
BEGIN CATCH
SET @continue = 10
SET @error = 'Error inserting Diary Attachment record: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
BEGIN TRY
DECLARE @iSL INT
DECLARE @data TABLE
([seq] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[folderid] INT NOT NULL,
[trackreference] INT NOT NULL)
BEGIN TRY
EXEC sp_xml_preparedocument @iSL OUTPUT, @pDocFolders
END TRY
BEGIN CATCH
EXEC sp_xml_preparedocument @iSL OUTPUT, ''
END CATCH
INSERT
INTO @data
([folderid],
[trackreference])
SELECT SRC.[folderid],
@inTrackRef
FROM OPENXML(@iSL, N'selectedfolders/folder')
WITH ([folderid] INT '@id') SRC
EXEC sp_xml_removedocument @iSL
INSERT
INTO [dbo].[DocFolderDocuments]
([DocFolderID],
[TrackReference])
SELECT DT.[folderid],
DT.[trackreference]
FROM @data DT
LEFT OUTER JOIN [dbo].[DocFolderDocuments] DFD
ON DFD.[DocFolderID] = DT.[folderid]
AND DFD.[TrackReference] = DT.[trackreference]
WHERE DFD.[TrackReference] IS NULL
END TRY
BEGIN CATCH
SET @continue = 11
SET @error = 'Error copying documents to document folder: ' + Error_Message()
END CATCH
END
SELECT @strcasecode AS [CaseCode],
@inTrackRef AS [NewTrackReference],
@strFileName As [Path],
@strUNCFileName As [UNCPath],
@continue AS [ErrorCode],
@error AS [ErrorMessage]
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'spGetNextTrackReferenceInDiaryAttachments'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[spGetNextTrackReferenceInDiaryAttachments]
END
GO
CREATE PROCEDURE
[dbo].[spGetNextTrackReferenceInDiaryAttachments]
AS
/**********************************************************************************
*
* [dbo].[spGetNextTrackReferenceInDiaryAttachments]
*
* Gets next available track referene number
*
* Modification History
* 2017-03-10 Pino Carafa Imported in Framework and corrected to
* use ky_NETGetNextTrackReference
*
**********************************************************************************/
BEGIN
DECLARE @TrackReference INT
EXEC @TrackReference = [dbo].[ky_NETGetNextTrackReference]
SELECT @TrackReference AS [TRACKREFERENCE]
--SELECT
-- ISNULL(MAX(TRACKREFERENCE), 0)+1 AS TRACKREFERENCE
--FROM
-- dbo.DiaryAttachments --ORDER BY TRACKREFERENCE DESC
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = 'ky_WDCreateDiaryAttachment4'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_WDCreateDiaryAttachment4]
END
GO
/***************************************************************************************************
*
* ky_WDCreateDiaryAttachment4
*
* To be used in conjunction with ky_WDCreateDiaryStep4. There are no equivalents for earlier
* ky_WDCreateDiaryStep versions.
*
* Modification History
* 2017-03-10 Pino Carafa Use ky_NETGetNextTrackreference
*
***************************************************************************************************/
CREATE PROCEDURE
[dbo].[ky_WDCreateDiaryAttachment4]
(@pDiaryID Int=0,
@pName varchar(10),
@pDocName varchar(100),
@pDocument varchar(100),
@pType varchar(10),
@pDocClass varchar(3),
@pDateEntered varchar(17),
@pEnterBy varchar(8),
@pLastAccessed varchar(17),
@pLastAccessedby varchar(8),
@pSystem varchar(1),
@pDictationFile varchar(255))
AS
BEGIN
SET NOCOUNT ON
DECLARE @strcasecode varchar(20)
DECLARE @inTrackRef Int
DECLARE @strFileName varchar(255)
DECLARE @strUNCFileName varchar(500)
DECLARE @strMatterNo varchar(10)
DECLARE @strClientCode varchar(10)
DECLARE @strDefaultFolder varchar(255)
DECLARE @strSource varchar(20)
DECLARE @strType varchar(3)
DECLARE @continue int
DECLARE @error varchar(1000)
SET @continue = 0
SET @error = ''
-- Start Transaction -----------------------------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
SET @pDiaryID = ISNULL(@pDiaryID, 0)
IF (@pDiaryID = 0)
BEGIN
SET @continue = 12
SET @error = 'invalid or missing Diary ID'
END
-- Get Diary and Matter information
IF (@continue = 0)
BEGIN
BEGIN TRY
SELECT @strcasecode = RTRIM(ISNULL(MAT.[code], '')),
@strClientCode = RTRIM(ISNULL(MAT.[clientcode], '')),
@strMatterNo = RTRIM(ISNULL(MAT.[matter], '')),
@strSource = RTRIM(ISNULL(DIA.[PROCESSTYPE], ''))
FROM [dbo].[diary] DIA
INNER JOIN [dbo].[matters] MAT
ON MAT.[code] = DIA.[CASECODE]
WHERE DIA.[ACTIONID] = @pDiaryID
SET @strcasecode = ISNULL(@strcasecode, '')
SET @strClientCode = ISNULL(@strClientCode, '')
SET @strMatterNo = ISNULL(@strMatterNo, '')
SET @strSource = CASE WHEN ISNULL(@strSource, 'I') = 'I'
THEN 'Received E-mail'
ELSE 'Sent E-mail' END
IF (@strcasecode = '')
BEGIN
SET @continue = 2
SET @error = 'invalid or missing Diary ID'
END
END TRY
BEGIN CATCH
SET @continue = 1
SET @error = 'Error retrieving Diary information for Diary ID: ' + ISNULL(convert(varchar(10), @pDiaryID), 'unknown') + ' ' + Error_Message()
END CATCH
END
-- Get Next TrackReference -----------------------------------------------------------------------------------------------------------------------------------------
IF (@continue = 0)
BEGIN
BEGIN TRY
EXEC @inTrackRef = [dbo].[ky_NETGetNextTrackReference]
--SELECT @inTrackRef = ISNULL(MAX(DAT.[TrackReference]), 0) + 1
-- FROM [dbo].[DiaryAttachments] DAT
END TRY
BEGIN CATCH
SET @continue = 3
SET @error = 'Error calculating next Track Reference: ' + Error_Message()
END CATCH
END
-- Write DiaryAttachment Record -----------------------------------------------------------------------------------------------------------------------------------------
IF (@continue = 0)
BEGIN
BEGIN TRY
Select @strDefaultFolder = (SELECT RTRIM(ISNULL(SC.[SCWPFOLDER], ''))
FROM [dbo].[SystemConfig] SC)
SET @strDefaultFolder = ISNULL(@strDefaultFolder, '')
IF (@strDefaultFolder = '')
BEGIN
SET @continue = 4
SET @error = 'SCWPFOLDER not set in System Config'
END
END TRY
BEGIN CATCH
SET @continue = 5
SET @error = 'Error retrieving SCWPFOLDER: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
BEGIN TRY
SELECT @strFileName = RTRIM(ISNULL(CLT.[DCOUMENTFOLDER], ''))
FROM [dbo].[client] CLT
WHERE CLT.[CLCODE] = @strClientCode
SET @strFileName = RTRIM(ISNULL(@strFileName, ''))
IF (@strFileName = '')
BEGIN
SET @strFileName = @strDefaultFolder + '\' + @strClientCode
UPDATE CLT
SET CLT.[DCOUMENTFOLDER] = @strFileName
FROM [dbo].[client] CLT
WHERE CLT.[CLCODE] = @strClientCode
END
END TRY
BEGIN CATCH
SET @continue = 6
SET @error = 'Error establishing Document Folder: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
SET @pDocument = RTRIM(ISNULL(@pDocument, ''))
SET @pDocument = REPLACE(@pDocument, char(13) + char(10), '_')
IF (@pDocument = '')
BEGIN
SET @continue = 7
SET @error = 'missing document name'
END
END
IF (@continue = 0)
BEGIN
SET @pDocName = RTRIM(ISNULL(@pDocName, ''))
SET @pDocName = REPLACE(@pDocName, char(13) + char(10), '_')
IF (@pDocName = '')
BEGIN
SET @continue = 7
SET @error = 'missing document name'
END
END
IF (@continue = 0)
BEGIN
BEGIN TRY
SET @pType = RTRIM(ISNULL(@pType, ''))
SET @strType = convert(varchar(3), @pType)
END TRY
BEGIN CATCH
SET @continue = 8
SET @error = 'Error interpreting Document Type: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
BEGIN TRY
SET @strFileName = @strFileName
+ '\'
+ @strMatterNo
+ '\'
+ @pDocument
+ '_'
+ convert(varchar(10), @inTrackRef)
IF (@pType <> '')
BEGIN
SET @strFileName = @strFileName
+ '.'
+ @pType
END
END TRY
BEGIN CATCH
SET @continue = 9
SET @error = 'Error establishing filename: ' + Error_Message()
END CATCH
END
SET @strFileName = ISNULL(@strFileName, '')
SET @strUNCFileName = @strFileName
IF (SUBSTRING(@strUNCFileName, 2, 1) = ':')
BEGIN
SELECT @strUNCFileName = ISNULL(MAX(UNC.[UNC]) + SUBSTRING(@strUNCFileName, 3, LEN(@strUNCFileName) - 2), @strUNCFileName)
FROM [dbo].[UNCAlias] UNC
WHERE SUBSTRING(@strUNCFileName, 1, 1) = UNC.[Drive]
SET @strUNCFileName = LEFT(@strUNCFileName, 2) + REPLACE(RIGHT(@strUNCFileName, LEN(@strUNCFileName) - 2), '\\', '\')
END
IF (@continue = 0)
BEGIN
BEGIN TRY
INSERT INTO DiaryAttachments
([DiaryId],
[TrackReference],
[CaseCode],
[Name],
[Document],
[Filepath],
[Type],
[DocClass],
[DateEntered],
[EnteredBy],
[LastAccessDate],
[LastAccessBy],
[System],
[Dictationfile],
[Source])
VALUES(@pDiaryID,
@inTrackRef,
@strcasecode,
@pName,
@pDocName,
@strFileName,
@strType,
@pDocClass,
convert(datetime, @pDateEntered),
@pEnterBy,
convert(datetime, @pLastAccessed),
@pLastAccessedby,
@pSystem,
@pDictationFile,
@strSource)
END TRY
BEGIN CATCH
SET @continue = 10
SET @error = 'Error inserting Diary Attachment record: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
BEGIN TRY
UPDATE DIA
SET DIA.[ATTACHMENTS] = CASE WHEN ATT.[TOTAL] < 2
THEN 'N'
ELSE 'Y' END
FROM [dbo].[diary] DIA
CROSS APPLY (SELECT COUNT(1) AS [TOTAL]
FROM [dbo].[DiaryAttachments] DAT
WHERE DAT.[DiaryID] = DIA.[ACTIONID]) ATT
WHERE DIA.[ACTIONID] = @pDiaryID
AND DIA.[ACTIONTYPE] = 'E'
END TRY
BEGIN CATCH
SET @continue = 13
SET @error = 'Error updating Attachments column in Diary: ' + Error_Message()
END CATCH
END
IF (@continue = 0)
BEGIN
BEGIN TRY
COMMIT
END TRY
BEGIN CATCH
SET @continue = 11
SET @error = 'Error committing transaction: ' + Error_Message()
END CATCH
END
IF (@continue <> 0)
BEGIN
BEGIN TRY
ROLLBACK
END TRY
BEGIN CATCH
SET @continue = 1300 + ISNULL(@continue, 0)
SET @error = @error + ', Unexpected error rolling back transaction: ' + Error_Message()
END CATCH
END
SELECT @inTrackRef AS [NewTrackReference],
@strFileName As [Path],
@strUNCFileName As [UNCPath],
@continue AS [ErrorCode],
@error AS [ErrorMessage]
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_WDCreateTranscription'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_WDCreateTranscription]
END
GO
Create Procedure
[dbo].[ky_WDCreateTranscription]
(@TrackReference int,
@extension varchar(10))
AS
/**************************************************************************************
*
* [dbo].[ky_WDCreateTranscription]
*
* Modification History
* 2015-10-23 Pino Carafa imported into Framework
* 2017-03-10 Pino Carafa use ky_NETGetNextTrackReference
*
**************************************************************************************/
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)
DECLARE @NextTrackReference INT
EXEC @NextTrackReference = [dbo].[ky_NETGetNextTrackReference]
INSERT
INTO [dbo].[DiaryAttachments]
([DIARYID],
[TRACKREFERENCE],
[CASECODE],
[NAME],
[DOCUMENT],
[FILEPATH],
[TYPE],
[DOCCLASS],
[DATEENTERED],
[ENTEREDBY],
[LASTACCESSDATE],
[LASTACCESSBY],
[SYSTEM],
[DICTATIONFILE],
[Source])
SELECT DAT.[DIARYID],
@NextTrackReference,
DAT.[CASECODE],
DAT.[NAME],
DAT.[DOCUMENT],
@FilePath + '_' + CONVERT(varchar(12), @NextTrackReference) + '.' + @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 [dbo].[DiaryAttachments] DAT
WHERE DAT.[TRACKREFERENCE] = @TrackReference
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_CreateDiaryAttachment'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_CreateDiaryAttachment]
END
GO
CREATE PROCEDURE [dbo].[ky_CreateDiaryAttachment]
(@pDiaryID Int=0,
@pCaseCode char(17),
@pName char(10),
@pDocument char(100),
@pFilePath char(255),
@pType char(4),
@pDocClass char(3),
@pDateEntered varchar(23),
@pEnterBy char(8),
@pLastAccessed varchar(23),
@pLastAccessedby char(8),
@pSystem char(1),
@pDictationFile char(255),
@pTrackRef int=0 output)
AS
/************************************************************************
*
* [dbo].[ky_CreateDiaryAttachment]
* Imported into Framework for backward compatibility
*
* Modification History
* 2017-03-10 Pino Carafa Use ky_NETGetNextTrackreference
*
************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @strcasecode nVarChar(17)
DECLARE @inTrackRef Int
DECLARE @strFileName varchar(255)
DECLARE @strMatterNo char(8)
DECLARE @strClientCode char(8)
DECLARE @strDefaultFolder varchar(255)
DECLARE @strSource varchar(20)
DECLARE @CaseCodeLen int
select @CaseCodeLen = sc.[length]
from sysobjects so
inner join syscolumns sc
on sc.[id] = so.[id]
and sc.[name] = 'casecode'
where so.[name] = 'diary'
-- Start Transaction -----------------------------------------------------------------------------------------------------------------------------------------
BEGIN TRAN Key_Tran_01
IF @@error <> 0 GOTO Tran_Fail
IF LEN(@pCaseCode) < 2 GOTO Tran_Fail
IF LEN(@pCaseCode) > @CaseCodeLen GOTO Tran_Fail
-- Get CaseCode From Matters Table -----------------------------------------------------------------------------------------------------------------------------------------
SELECT @strCaseCode = convert(nvarchar(17), (SELECT Top 1 [Code]
FROM [dbo].[Matters]
WHERE [code] = @pCaseCode ))
IF @@error <> 0 GOTO Tran_Fail
IF LEN( @strCaseCode ) < 2 GOTO Tran_Fail
SELECT @strClientCode = (SELECT Top 1 [ClientCode]
FROM [dbo].[Matters]
WHERE [code] = @pCaseCode )
IF @@error <> 0 GOTO Tran_Fail
IF LEN( @strCaseCode ) < 2 GOTO Tran_Fail
SELECT @strMatterNo = (SELECT Top 1 [Matter]
FROM [dbo].[Matters]
WHERE [code] = @pCaseCode )
IF @@error <> 0 GOTO Tran_Fail
IF LEN( @strMatterNo ) < 2 GOTO Tran_Fail
-- Get Next TrackReference -----------------------------------------------------------------------------------------------------------------------------------------
--SELECT @inTrackRef = ( SELECT Top 1
-- TrackReference
-- FROM DiaryAttachments
-- ORDER BY TrackReference DESC)
--IF @@error <> 0 GOTO Tran_Fail
--IF @inTrackRef IS NULL
-- BEGIN
-- SET @inTrackRef = 0
-- END
--SET @inTrackRef = @inTrackRef + 1
EXEC @inTrackRef = [dbo].[ky_NETGetNextTrackReference]
-- Write DiaryAttachment Record -----------------------------------------------------------------------------------------------------------------------------------------
Select @strDefaultFolder = (SELECT SCWPFOLDER
FROM SystemConfig)
Select @strFileName = (SELECT DCOUMENTFOLDER
FROM client
WHERE CLCODE = @strClientCode)
If rtrim(@strFileName) = ''
BEGIN
Update client
set DCOUMENTFOLDER = rtrim(@strDefaultFolder)
+ '\'
+ @strClientCode
where CLCODE = @strClientCode
END
SET @pDocument = REPLACE(@pDocument, char(13) + char(10), '_')
Set @strFileName = rtrim((SELECT DCOUMENTFOLDER
FROM client
WHERE CLCODE = @strClientCode))
+ '\'
+ rtrim(@strMatterNo)
+ '\'
+ rtrim(@pDocument)
+ '_'
+ rtrim(CAST(@inTrackRef as char))
+ '.'
+ rtrim(@pType)
Select @strSource = (Select ProcessType
from Diary
where actionid = @pDiaryId)
If rtrim(@strSource) = 'I'
BEGIN
SET @strSource = 'Received E-mail'
END
else
BEGIN
SET @strSource = 'Sent E-mail'
END
INSERT INTO DiaryAttachments
([DiaryId],
[TrackReference],
[CaseCode],
[Name],
[Document],
[Filepath],
[Type],
[DocClass],
[DateEntered],
[EnteredBy],
[LastAccessDate],
[LastAccessBy],
[System],
[Dictationfile],
[Source])
Values(@pDiaryID,
@inTrackRef,
@pCaseCode,
@pName,
@pDocument,
@strFileName,
left(@pType,3),
@pDocClass,
convert(datetime, @pDateEntered),
@pEnterBy,
convert(datetime, @pLastAccessed),
@pLastAccessedby,
@pSystem,
@pDictationFile,
@strSource)
IF @@error <> 0 GOTO Tran_Fail
-- Commit Transaction ------------------------------------------------------------------------------------------------------------------
COMMIT TRAN Key_Tran_01
IF @@error <> 0 GOTO Tran_Fail
Set @pTrackRef = @inTrackRef
-- End Of Processing --------------------------------------------------------------------------------------------------------------------
ExitPoint:
Return
-- Error Processing -------------------------------------------------------------------------------------------------------------------------------------
Tran_Fail:
rollback tran Key_Tran_01
Set @pTrackRef = -1
goto ExitPoint
END
GO
IF OBJECT_ID(N'[ky_NETGetMyDayBook]',N'P')IS NOT NULL
DROP PROCEDURE [ky_NETGetMyDayBook]
GO
CREATE PROCEDURE [dbo].[ky_NETGetMyDayBook]
(@FECode VARCHAR(10),
@Post CHAR(1) ='N'
)
AS
/*
Created By : Praveen Yadav.P
Date : 11/04/2014
Description : ky_NETGetMyDayBook
Modification History
--------------------------
Modified By : - Praveen yadav.P
Modified Date : - 02FEB2015
Modification Note: - Modified @DayBookValue parameter type to float from int
23JUN2015 Sridharen KEYD - 2332 - Use of LTRIM(RTRIM(
* 05FEB2016 Arun Specified the Varchar size
*/
BEGIN
DECLARE @DayBookValue FLOAT
SELECT
@DayBookValue=ISNULL(SUM(TIME),0)
FROM
Timedaybook WITH (NOLOCK)
WHERE
FEEEARN=@FECode
IF(@DayBookValue=0)
BEGIN
SET @DayBookValue=1
END
SELECT
CASE
WHEN TIME>59
THEN
--CASE
-- WHEN
-- LEN(CAST(FLOOR((TIME / 60)) AS VARCHAR(8)))=1
-- THEN
-- '0'+CAST(FLOOR((TIME / 60)) AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((TIME % 60) AS VARCHAR(2)), 2)
-- ELSE
-- CAST(FLOOR((TIME / 60)) AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((TIME % 60) AS VARCHAR(2)), 2)
CASE
WHEN LEN(CAST(FLOOR(([TIME] / 60)) AS VARCHAR(8)))=0
THEN '00:'+CAST(FLOOR(([TIME] / 60)) AS VARCHAR(8))
WHEN
LEN(CAST(FLOOR(([TIME] / 60)) AS VARCHAR(8)))=1
THEN
'0'+CAST(FLOOR(([TIME] / 60)) AS VARCHAR(8)) + ':' + RIGHT('0' + CAST(([TIME] % 60) AS VARCHAR(2)), 2)
ELSE
CAST(FLOOR(([TIME] / 60)) AS VARCHAR(8))+':' + RIGHT('0' + CAST(([TIME] % 60) AS VARCHAR(2)), 2)
END
ELSE
CASE
WHEN
LEN(CAST([TIME] AS VARCHAR(30)))=1
THEN
'00:0' + CAST([TIME] AS VARCHAR(30))
ELSE
'00:' + CAST([TIME] AS VARCHAR(30))
END
END AS CaseTime,
(RTRIM(ISNULL(Name,''))+' '+RTRIM(ISNULL([Description],''))) AS MatterDetails,
CEILING ((ISNULL([TIME],0)/@DayBookValue*100)) AS Progress,
StopwatchStatus,
TDB.RecordID,
TDB.[DATE],
TDB.MATTER,
CASE
WHEN TDB.MATTER='~' THEN 'Admin Time'
ELSE TDB.MATTER END AS DisplayMatter,
C.Name,
TDB.COMMENT,
TDB.CHARGE,
UPPER(ISNULL(TIMEORCHARGE,'T')) AS TimeorCharge
FROM
Timedaybook TDB WITH (NOLOCK)
LEFT OUTER JOIN
Matters M WITH (NOLOCK)
ON
TDB.MATTER=M.Code
LEFT OUTER JOIN
Contacts C
ON
SUBSTRING(TDB.MATTER,0,CHARINDEX('/',TDB.MATTER)) =C.Code
WHERE
FEEEARN=@FECode
AND
ISNULL(Post,'Y')
IN
('Y',@Post)
ORDER BY RECORDID
SELECT
ISNULL(REC_IRR,'C') AS ChargeType,
CASE
WHEN SUM([TIME])>59 THEN
CASE
WHEN LEN(CAST(FLOOR((SUM([TIME]) / 60)) AS VARCHAR(8)))=0
THEN '00:'+CAST(FLOOR((SUM([TIME]) / 60)) AS VARCHAR(8))
WHEN
LEN(CAST(FLOOR((SUM([TIME]) / 60)) AS VARCHAR(8)))=1
THEN
'0'+CAST(FLOOR((SUM([TIME]) / 60)) AS VARCHAR(8)) + ':' + RIGHT('0' + CAST((SUM([TIME]) % 60) AS VARCHAR(2)), 2)
ELSE
CAST(FLOOR((SUM([TIME]) / 60)) AS VARCHAR(8))+':' + RIGHT('0' + CAST((SUM([TIME]) % 60) AS VARCHAR(2)), 2)
END
ELSE
CASE
WHEN
LEN(CAST(SUM([TIME]) AS VARCHAR(30)))=1
THEN
'00:0' + CAST(SUM([TIME]) AS VARCHAR(30))
ELSE
'00:' + CAST(SUM([TIME]) AS VARCHAR(30))
END
END AS CaseTime
FROM
Timedaybook WITH (NOLOCK)
WHERE
FEEEARN=@FECode
AND
ISNULL(Post,'Y')
IN
('Y',@Post)
GROUP BY
REC_IRR
ORDER BY
ChargeType
SELECT @DayBookValue AS DayBookValue
END
GO
-- Sometimes the information in the exception log gets truncated just when it's
-- about to go into the really important information.
--
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] [SO]
INNER JOIN SYS.[columns] [SC]
ON [SC].[object_id] = [SO].[object_id]
AND [SC].[name] = 'ExceptionMessage'
AND [SC].[max_length] > -1
WHERE [SO].[name] = 'ExceptionLog'
AND [SO].[type] = 'U')
BEGIN
EXEC('
ALTER
TABLE [dbo].[ExceptionLog]
ALTER COLUMN [ExceptionMessage] VARCHAR(MAX) NOT NULL
')
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] [SO]
INNER JOIN SYS.[columns] [SC]
ON [SC].[object_id] = [SO].[object_id]
AND [SC].[name] = 'Source'
AND [SC].[max_length] > -1
WHERE [SO].[name] = 'ExceptionLog'
AND [SO].[type] = 'U')
BEGIN
EXEC('
ALTER
TABLE [dbo].[ExceptionLog]
ALTER COLUMN [Source] VARCHAR(MAX) NOT NULL
')
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] [SO]
INNER JOIN SYS.[columns] [SC]
ON [SC].[object_id] = [SO].[object_id]
AND [SC].[name] = 'MethodName'
AND [SC].[max_length] > -1
WHERE [SO].[name] = 'ExceptionLog'
AND [SO].[type] = 'U')
BEGIN
EXEC('
ALTER
TABLE [dbo].[ExceptionLog]
ALTER COLUMN [MethodName] VARCHAR(MAX) NOT NULL
')
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] [SO]
INNER JOIN SYS.[columns] [SC]
ON [SC].[object_id] = [SO].[object_id]
AND [SC].[name] = 'StackTrace'
AND [SC].[max_length] > -1
WHERE [SO].[name] = 'ExceptionLog'
AND [SO].[type] = 'U')
BEGIN
EXEC('
ALTER
TABLE [dbo].[ExceptionLog]
ALTER COLUMN [StackTrace] VARCHAR(MAX) NOT NULL
')
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'spInsertErrorLog'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[spInsertErrorLog]
END
GO
CREATE PROCEDURE
[dbo].[spInsertErrorLog]
(@CurrentLogOn VARCHAR(100),
@ExceptionMessage VARCHAR(MAX),
@Source VARCHAR(MAX),
@MethodName VARCHAR(MAX),
@StackTrace VARCHAR(MAX))
AS
/******************************************************************************************
*
* [dbo].[spInsertErrorLog]
*
* Store error information into the Exception Log
*
* Modification History
* 2017-03-20 Pino Carafa Created from spInsertErrorLog
* make all fields VARCHAR(MAX)
*
******************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @Date DATETIME
SET @Date = GETDATE()
SET @ExceptionMessage = RTRIM(ISNULL(@ExceptionMessage, ''))
SET @MethodName = RTRIM(ISNULL(@MethodName, ''))
SET @StackTrace = RTRIM(ISNULL(@StackTrace, ''))
SET @Source = RTRIM(ISNULL(@Source, ''))
SET @CurrentLogon = RTRIM(ISNULL(@CurrentLogon, ''))
IF @CurrentLogon = ''
BEGIN
SELECT @CurrentLogon = CASE WHEN CHARINDEX(CHAR(0), CONVERT(VARCHAR(MAX), [P].[context_info])) > 0
THEN SUBSTRING(CONVERT(VARCHAR(MAX), [P].[context_info]), 1, CHARINDEX(CHAR(0), CONVERT(VARCHAR(MAX), [P].[context_info])) - 1)
ELSE CONVERT(VARCHAR(MAX), [P].[context_info]) END
FROM [master].[dbo].[SYSPROCESSES] [P]
WHERE [P].[spid] = @@SPID
END
SET @CurrentLogon = RTRIM(ISNULL(@CurrentLogon, ''))
IF @CurrentLogon = ''
BEGIN
SELECT @CurrentLogon = CONVERT(VARCHAR(100),
CASE WHEN RTRIM(ISNULL([P].[nt_domain], '')) = ''
THEN ''
ELSE RTRIM(ISNULL([P].[nt_domain], '')) END
+ CASE WHEN RTRIM(ISNULL([P].[nt_username], '')) = ''
THEN ''
ELSE '/' + RTRIM(ISNULL([P].[nt_username], '')) END
+ CASE WHEN RTRIM(ISNULL([P].[hostname], '')) = ''
THEN ''
ELSE ' at ' + RTRIM(ISNULL([P].[hostname], '')) END)
FROM [master].[dbo].[SYSPROCESSES] [P]
WHERE [P].[spid] = @@SPID
END
INSERT
INTO [dbo].[ExceptionLog]
([CurrentLogOn],
[ExceptionDate],
[ExceptionMessage],
[Source],
[MethodName],
[StackTrace])
VALUES(@CurrentLogOn,
@Date,
@ExceptionMessage,
@Source,
@MethodName,
@StackTrace)
SET NOCOUNT OFF
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_NETSPXMLAddTimeDayBook'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETSPXMLAddTimeDayBook]
END
GO
CREATE PROCEDURE
[dbo].[ky_NETSPXMLAddTimeDayBook]
(@XML NVARCHAR(MAX))
AS
BEGIN
/*************************************************************************************************************
*
* Example:
* exec [dbo].[ky_NETSPXMLAddTimeDayBook] N''
* Used to add new Timedaybook entry
*
* Modification History
* 2014-04-28 Praveen Yadav.P originally Created as ky_NETAddTimeDayBook
* 2014-12-15 Pino Carafa Matter Code can be up to 20 characters.
* 2015-06-10 Magesh Added two new parameter @period and @year to update
* year and period value in timedaybook table while insert
* 2015-06-23 Sridharen KEYD - 2332 - Use of LTRIM(RTRIM(
* 2015-08-27 Pino Carafa Improve readability
* Set default logging information
* 2015-03-16 Magesh Included an condition to check whether the record in time or charge.
* 2016-04-01 Suriya KEYD-3398-Allow users to have default value for chargeable/non-chargeable time
* 2016-09-09 Arun Have removed the condition to add blank timer
* 2016-09-21 Arun http://jira1.dsrc.in:8080/browse/KEYD-4063
* 2016-11-08 Arun http://jira1.dsrc.in:8080/browse/KEYD-4217
*
* 2017-01-26 Pino Carafa imported to Framework using XML parameter instead of individual parameters
* 2017-01-26 Pino Carafa added new task attribute. Default blank
* added new comment attribute. Default blank
* 2017-03-20 John Ginnane Added setting for new time to use handler's default task code - KEYD-4482
* 2017-03-22 John Ginnane Should only bring in the task of the current handler
*
*************************************************************************************************************/
DECLARE @iSL INT
DECLARE @Matter VARCHAR(20)
DECLARE @FeeEarner VARCHAR(10)
DECLARE @Minutes INT
DECLARE @StopwatchStatus INT
DECLARE @RecordID INT
DECLARE @UpdateByPass BIT
DECLARE @DeleteByPass BIT
DECLARE @AddNew BIT
DECLARE @Task VARCHAR(6)
DECLARE @Comment VARCHAR(1000)
BEGIN TRY
EXEC sp_xml_preparedocument @iSL OUTPUT, @XML
END TRY
BEGIN CATCH
EXEC sp_xml_preparedocument @iSL OUTPUT, N''
END CATCH
SELECT @Matter = [SRC].[Matter],
@FeeEarner = [SRC].[FeeEarner],
@Minutes = [SRC].[Minutes],
@StopwatchStatus = [SRC].[StopwatchStatus],
@RecordID = [SRC].[RecordID],
@UpdateByPass = [SRC].[UpdateByPass],
@DeleteByPass = [SRC].[DeleteByPass],
@AddNew = [SRC].[AddNew],
@Task = [SRC].[Task],
@Comment = [SRC].[Comment]
FROM OPENXML(@iSL, N'tdb')
WITH ([Matter] VARCHAR(20) '@matter',
[FeeEarner] VARCHAR(10) '@feeearner',
[Minutes] INT '@minutes',
[StopwatchStatus] INT '@stopwatchstatus',
[RecordID] INT '@recordid',
[UpdateByPass] INT '@updatebypass',
[DeleteByPass] INT '@deletebypass',
[AddNew] INT '@addnew',
[Task] VARCHAR(6) '@task',
[Comment] VARCHAR(1000) '@comment') [SRC]
EXEC sp_xml_removedocument @iSL
--Original defaults
SET @Minutes = ISNULL(@Minutes, 0)
SET @StopwatchStatus = ISNULL(@StopwatchStatus, 1)
SET @RecordID = ISNULL(@RecordID, 0)
SET @UpdateByPass = ISNULL(@UpdateByPass, 0)
SET @DeleteByPass = ISNULL(@DeleteByPass, 0)
SET @AddNew = ISNULL(@AddNew, 0)
IF NOT EXISTS
(SELECT TOP 1 1
FROM [dbo].[TasksCodes] [TC]
WHERE [TC].[CODE] = @Task)
BEGIN
SET @Task = ''
-- If "AutoDefaultTaskTime" is enabled in the settings then use the handler's default task
IF (SELECT TOP 1 ISNULL(CFG.[KeyValue], 'False')
FROM [dbo].[Settings] AS CFG
WHERE CFG.[KeyName] = 'AutoDefaultTaskTime') = 'True'
BEGIN
SELECT @Task = ISNULL(HAN.[DEFAULTTASK], ''),
@Comment = ISNULL(TSK.[DESC], '')
FROM [dbo].[Handlers] AS HAN
INNER JOIN [dbo].[TasksCodes] AS TSK
ON HAN.[DEFAULTTASK] = TSK.[CODE]
WHERE HAN.[Code] = @FeeEarner
END
END
ELSE
BEGIN
SELECT @TASK = RTRIM([TC].[CODE])
FROM [dbo].[TasksCodes] [TC]
WHERE [TC].[CODE] = @TASK
END
IF @UpdateByPass = 0
BEGIN
UPDATE [TDB]
SET [TDB].[StopwatchStatus] = 1
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[FeeEarn] = @FeeEarner
END
IF @DeleteByPass = 0
BEGIN --IF @DeleteByPass = 0
IF(@Minutes=0)
BEGIN
DELETE [TDB]
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[FeeEarn] = @FeeEarner
AND ISNULL([TDB].[Time], 0) = 0
AND [TDB].[Matter] <> ''
AND [TDB].[TimeOrCharge] = 'T'
--AND [TDB].[Matter] <> @Matter
END
ELSE
BEGIN
DELETE [TDB]
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[FeeEarn] = @FeeEarner
AND ISNULL([TDB].[Time], 0) = 0
AND [TDB].[Matter] NOT IN ('' , @Matter)
AND [TDB].[TimeOrCharge] = 'T'
END
END --IF @DeleteByPass = 0
IF @RecordID <> 0
BEGIN -- IF @RecordID <> 0
UPDATE [TDB]
SET [TDB].[StopwatchStatus] = @StopwatchStatus,
[TDB].[Time] = @Minutes,
[TDB].[Charge] = (ISNULL([TDB].[Rate], 0) * (@Minutes / 60.0)),
[TDB].[Post] = 'Y'
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[RecordID] = @RecordID
SELECT TOP 1
[TDB].[RecordID],
ISNULL([TDB].[Time], 0) AS [Minutes]
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[RecordID] = @RecordID
END --IF @RecordID <> 0
ELSE -- ELSE CONDITION: IF @RecordID <> 0
BEGIN -- ELSE CONDITION: IF @RecordID <> 0
IF (NOT EXISTS
(SELECT TOP 1 1
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[FeeEarn] = @FeeEarner
AND RTRIM([TDB].[Matter]) = @Matter
AND [TDB].[Post] = 'Y'
AND [TDB].[TIMEORCHARGE]='T')
OR @Matter=''
OR @AddNew=1) --Insert conditions
BEGIN --Insert conditions
DECLARE @PERIOD AS INT
DECLARE @YEAR AS INT
DECLARE @IsSAM4 bit
SET @IsSAM4 = [dbo].[ISSAM4]()
IF @IsSAM4 = 0
BEGIN
SELECT @PERIOD = [CL].[CURPER],
@YEAR = [CL].[YEAR]
FROM [dbo].[control] [CL]
END
ELSE
BEGIN
SELECT @PERIOD = [CL].[numvalue]
FROM [dbo].[CtrlNum] [CL]
WHERE [CL].[code] = 'CurPeriod'
SELECT @YEAR = [CL].[numvalue]
FROM [dbo].[CtrlNum] [CL]
WHERE [CL].[code] = 'CurYear'
END
SET @PERIOD = ISNULL(@PERIOD, 0)
SET @YEAR = ISNULL(@YEAR, 0)
DECLARE @IsChargeable VARCHAR(1)
SELECT @IsChargeable = [HAN].[ISChargeable]
FROM [dbo].[Handlers] [HAN]
WHERE [HAN].[CODE] = @FeeEarner
SET @IsChargeable = ISNULL(@IsChargeable, 'C')
DECLARE @MatterBillingMethod CHAR(1)
DECLARE @ClientBillingMethod CHAR(1)
DECLARE @DefaultBillingMethod CHAR(1)
SELECT @MatterBillingMethod = RTRIM(ISNULL([MAT].[BillingMethod], '')),
@ClientBillingMethod = RTRIM(ISNULL([CON].[BillingMethod], ''))
FROM [dbo].[Matters] [MAT]
LEFT OUTER JOIN [dbo].[Contacts] [CON]
ON [CON].[Code] = [MAT].[ClientCode]
WHERE [MAT].[Code] = @Matter
SET @MatterBillingMethod = ISNULL(@MatterBillingMethod, '')
SET @ClientBillingMethod = ISNULL(@ClientBillingMethod, '')
IF(@MatterBillingMethod <> '')
BEGIN
SET @DefaultBillingMethod = @MatterBillingMethod
END
ELSE IF(@ClientBillingMethod <> '')
BEGIN
SET @DefaultBillingMethod = @ClientBillingMethod
END
ELSE
BEGIN
SET @DefaultBillingMethod = 'T'
END
INSERT
INTO [dbo].[TimeDayBook]
([Matter],
[FeeEarn],
[Date],
[Time],
[Task],
[TimeOrCharge],
[Rec_Irr],
[Post],
[StopwatchStatus],
[Period],
[Year],
[MatterLevel],
[Comment])
SELECT NW.[MATTER],
NW.[FeeEarn],
NW.[Date],
NW.[Time],
NW.[Task],
NW.[TimeOrCharge],
@IsChargeable,
NW.[Post],
NW.[StopwatchStatus],
NW.[Period],
NW.[Year],
MAT.[ChargeLevel],
NW.[Comment]
FROM (SELECT @Matter AS [MATTER],
@FeeEarner AS [FeeEarn],
CONVERT(VARCHAR(12), GETDATE(), 112) AS [Date],
@Minutes AS [Time],
@Task AS [Task],
@DefaultBillingMethod AS [TimeOrCharge],
'Y' AS [Post],
@StopwatchStatus AS [StopwatchStatus],
@PERIOD AS [Period],
@YEAR As [Year],
@Comment AS [Comment]) NW
LEFT OUTER JOIN [dbo].[matters] MAT
ON MAT.[Code] = NW.[MATTER]
SELECT SCOPE_IDENTITY() AS [RecordID],
0 AS [Minutes]
END --Insert conditions
ELSE --(ELSE CONDITION) Insert conditions
BEGIN --(ELSE CONDITION) Insert conditions
SET @RecordID = NULL
SELECT TOP 1
@RecordID = [TDB2].[RecordID]
FROM [dbo].[TimeDayBook] [TDB2]
WHERE [TDB2].[FEEEARN] = @FeeEarner
AND [TDB2].[MATTER] = @Matter
AND [TDB2].[TIMEORCHARGE] = 'T'
ORDER BY [TDB2].[RecordID] DESC
IF @RecordID IS NOT NULL
BEGIN
UPDATE [TDB]
SET [TDB].[StopwatchStatus] = @StopwatchStatus,
[TDB].[Post] = 'Y'
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[RecordID] = @RecordID
SELECT [TDB].[RecordID],
ISNULL([TDB].[Time], 0) AS [Minutes]
FROM [dbo].[TimeDayBook] [TDB]
WHERE [TDB].[RecordID] = @RecordID
END
END --(ELSE CONDITION) Insert conditions
END -- ELSE CONDITION: IF @RecordID <> 0
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_NETSPGetCaseUDFsForSearch'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETSPGetCaseUDFsForSearch]
END
GO
CREATE PROCEDURE
[dbo].[ky_NETSPGetCaseUDFsForSearch]
(@matter VARCHAR(20))
AS
/**************************************************************************************
*
* [dbo].[ky_NETSPGetCaseUDFsForSearch]
*
* Lists the Case UDF Fields at the bottom of the Matter selection pages
*
* Modification History
* 2017-03-07 Suriya KEYD-4491-Search screen UDF section
* NOT showing Dates format correctly
* 2017-03-24 Pino Carafa Handle values over 10,000,000
*
**************************************************************************************/
BEGIN
SELECT RTRIM(ISNULL(SUF.[PROMPT], '')) AS [Prompt],
CASE WHEN ISNULL(SUF.[Type], '') = 'Numeric'
THEN CASE WHEN CUF.[NumberValue] IS NULL
THEN RTRIM(ISNULL(CONVERT(VARCHAR(MAX), CUF.[TEXT1]), ''))
ELSE CONVERT(VARCHAR(20), ISNULL(CUF.[NumberValue], 0)) END --2017-03-24
WHEN ISNULL(SUF.[Type], '') <> 'Date'
THEN RTRIM(ISNULL(LUP.[Description], RTRIM(ISNULL(CONVERT(VARCHAR(MAX), CUF.[TEXT1]), ''))))
WHEN NOT CONVERT(VARCHAR(20),ISNULL(CUF.[NumberValue],0)) = '0.00'
THEN CONVERT(VARCHAR, DATEADD(dd, ISNULL(CUF.[NumberValue], 0), '18001228'), 106)
ELSE RTRIM(ISNULL(CONVERT(VARCHAR(MAX), CUF.[TEXT1]), '')) END AS [Value],
RTRIM(ISNULL(CONVERT(VARCHAR(MAX), CUF.[TEXT1]), '')) AS [Text]
FROM [dbo].[CaseUDFAnswers] CUF
LEFT OUTER JOIN [dbo].[SystemUserDefinedFields] SUF
ON SUF.[FILEPREFIX] = CUF.[UDFFILE]
AND SUF.[FIELDNAME] = CUF.[UDFNAME]
LEFT OUTER JOIN [dbo].[UDFLookups] LUP
ON LUP.[FieldName] = CONVERT(VARCHAR(30), CUF.[UDFNAME])
AND LUP.[CODE] = CONVERT(VARCHAR(3), CUF.[Text1])
WHERE CUF.[CASECODE] = @matter
AND SUF.[COMMON] = 1
ORDER BY CUF.[SEQNO]
END
GO
IF EXISTS
(SELECT TOP 1 1
FROM SYS.[objects] SO
WHERE SO.[name] = N'ky_NETDashboardCaseAlert'
AND SO.[type] = 'P')
BEGIN
DROP PROCEDURE [dbo].[ky_NETDashboardCaseAlert]
END
GO
CREATE PROCEDURE
[dbo].[ky_NETDashboardCaseAlert]
(@Handler VARCHAR(10),
@Function VARCHAR(50))
AS
/*************************************************************************************************
* [dbo].[ky_NETDashboardCaseAlert]
* Retrieves Detailed information for the entries displayed on the Dashboard (My Overview) page
*
* Pino 2014-11-19 - changed statements for Open Matters and Dormant Matters.
* assigned task to review remaining select statements
*
* Magesh 2015-05-05 - Changed conditions to get data more specifically
*
* Pino 2015-05-26 - SAM4 calculations
*
* Pino 2015-06-24 - Include Outlay Balance
*
* Pino 2015-06-25 - Fix drilldown for Clients Requiring Money Laundering Checks
* Exclude zero balances from Outlay Balance drilldown
*
* Magesh 2015-08-31 - Added condition to the query used to find out @TotalStatuteLimits to exculed min date values '1900-01-01'
*
* Magesh 2015-09-15 - Added three more function for fees for current month , last month and year to date.
*
* Magesh 2015-10-10 - Added three more funtions to show timedaybook and time enty for week,month and year.
*
* Magesh 2015-10-09 - Added three more funtions to show Admin time enty for week,month and year.
*
* Magesh 2015-10-16 - Added extra funtions to show effectively complete matters.
*
* John 2015-11-09 - Fixed function 'TimeDayBookforWeek' looking for 'ADM' handler instead of @Handler
*
* John 2015-11-19 - KEYD-2939 - Changed the inner join from batchdetails to matters to now be
* batchh to matters
* John 2015-11-25 - KEYD-2943 - Wrapped a number of financial figures in new function )
*
* John 2016-01-11 - Undid previous change as the VB .NET handles this now.
* It was also messing up totals for the figures (e.g. can't add "(-123.45)" to "512.12")
* Also excluding matters with no WIP from the WIP drill down
*
* John 2016-02-02 - Now excluding close matters from undertakings and statute approaching
* http://jira1.dsrc.in:8080/browse/KEYD-3158
*
* Arun 2016-02-05 - Specified the Varchar size
*
* Pino 2016-04-11 - Ignore No Estimate Fee calculation for system matters like ZZZZZZ/ZZZ2 and
* for the "zero" matters
*
* Pino 2016-04-12 - Exclude further "zero" matters
*
* Suriya 2016-04-26 - KEYD-3536-In the My Overview page the "Admin time recorded this week" drill down list is not showing any details.
*
* Arun 2016-05-16 - Have added OSFees and Debtors Days in Dashboard alert
*
* John 2016-08-02 - Code refactor
*
* Sridharan 2016-08-16 - KEYD:3591: In "My over view" page the "Fee" column of the Fees issued Year to Date
* drill down list is displayed wrong.
*
* John 2016-09-26 - KEYD-4090 Fees issued must now be posted before appearing in My Overview
*************************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @NCOMMAND NVARCHAR(MAX)
DECLARE @TODAYNUM INT
DECLARE @MONDAY DATETIME
DECLARE @STARTOFMONTH DATETIME
DECLARE @STARTOFYEAR DATETIME
DECLARE @GETDATE DATETIME
SET @Function =(SELECT UPPER(@Function))
IF @Function='OPENMATTER'
SELECT RTRIM(ISNULL(MAT.[Code], '')) AS [Code],
RTRIM(ISNULL(CON.[Name], '')) AS [Name],
RTRIM(ISNULL(MAT.[Description], '')) AS [Description]
FROM [dbo].[matters] MAT
LEFT OUTER JOIN [dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
WHERE MAT.[FECode] = @Handler
AND MAT.[Closed] = 'N'
AND MAT.[ClientCode] <> 'ZZZZZZ'
AND CASE WHEN isnumeric(MAT.[matter]) = 0
THEN 1
WHEN convert(int, MAT.[matter]) = 0
THEN 1
ELSE 0 END = 0
AND MAT.Closed <> 'Y'
IF @Function='EffectivelyComplete'
SELECT RTRIM(ISNULL(MAT.[Code], '')) AS [Code],
RTRIM(ISNULL(CON.[Name], '')) AS [Name],
RTRIM(ISNULL(MAT.[Description], '')) AS [Description]
FROM [dbo].[matters] MAT
LEFT OUTER JOIN [dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
WHERE MAT.[FECode] = @Handler
AND ISNULL(MAT.[PCODE], '') = ''
AND ISNULL(MAT.[PFECODE], '') = ''
AND CASE WHEN isnumeric(MAT.[matter]) = 0
THEN 1
WHEN convert(int, MAT.[matter]) = 0
THEN 1
ELSE 0 END = 0
AND MAT.Closed <> 'Y'
Else IF @Function='DORMANTMATTER'
SELECT RTRIM(ISNULL(MAT.[Code], '')) AS [Code],
RTRIM(ISNULL(CON.[Name], '')) AS [Name],
RTRIM(ISNULL(MAT.[Description], '')) AS [Description],
LAC.LastActionDate
FROM [dbo].[matters] MAT
LEFT OUTER JOIN [dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
CROSS APPLY (SELECT CASE WHEN MAX(DIA.[DATE]) IS NULL
THEN 1
ELSE CASE WHEN DATEDIFF(DD, MAX(DIA.[DATE]), GetDate()) < 180
THEN 0
ELSE 1 END END AS [Dormant],
MAX(DIA.[DATE]) AS [LastActionDate]
FROM [dbo].[diary] DIA
WHERE DIA.[CASECODE] = MAT.[Code]) LAC
WHERE MAT.[FECode] = @Handler
AND LAC.[Dormant] = 1
AND MAT.Closed <> 'Y'
ELSE IF @Function='INVOICEDATEPASSED'
SELECT MAT.Code, RTRIM(ISNULL([CON].[Name], '')) AS Name
,RTRIM(ISNULL([MAT].[Description],'')) AS [Description]
,ISNULL(MAT.ExpBillD,'') as ExpectedBillDate
,ISNULL(EstFee,0) as EstimatedFee
,ISNULL(ChargeBal,0) as WIP
FROM [dbo].[matters] MAT
LEFT JOIN
[dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
WHERE (FECode = @Handler)
AND (ExpBillD <= GETDATE())
AND MAT.Closed <> 'Y'
ELSE IF @Function='NOESTIMATEFEE'
SELECT MAT.Code,RTRIM(ISNULL([CON].[Name],'')) AS Name
,RTRIM(ISNULL([MAT].[Description],'')) AS [Description]
FROM [dbo].[matters] MAT
LEFT JOIN
[dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
WHERE (FECode = @Handler)
AND CASE WHEN isnumeric(MAT.[matter]) = 0
THEN 1
WHEN convert(int, MAT.[matter]) = 0
THEN 1
ELSE MAT.[EstFee] END = 0
AND MAT.Closed <> 'Y'
ELSE IF @Function='APPROACHINGESTIMATEFEE'
SELECT MAT.Code
,RTRIM(ISNULL([CON].[Name],'')) AS Name
,RTRIM(ISNULL([MAT].[Description],'')) AS [Description]
,ISNULL(EstFee,0) AS EstimatedFee
,ISNULL(ChargeBal,0) AS WIP
,CAST(CAST((COALESCE ((ISNULL(ChargeBal, 0) / NULLIF (EstFee, 0) * 100), 0)) AS DECIMAL(18,2))AS VARCHAR(100)) +'%' AS PercentageOfEstimatedFee
FROM [dbo].[matters] MAT
LEFT JOIN
[dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
WHERE (FECode = @Handler)
AND (COALESCE ((ISNULL(ChargeBal, 0) / NULLIF (EstFee, 0) * 100), 0)) >(SELECT ISNULL(KeyValue, 0)
FROM Settings
WHERE keyname = 'EstFeeWarningLimit')
AND MAT.Closed <> 'Y'
ELSE IF @Function='APPROACHINGLOCKEDUP'
SELECT MAT.Code
,RTRIM(ISNULL([CON].[Name],'')) AS Name
,RTRIM(ISNULL(MAT.[Description],'')) AS [Description]
FROM matters MAT
LEFT JOIN
Contacts CON
ON CON.[Code] = MAT.[ClientCode]
WHERE (FECode = @Handler)
AND (ISNULL(MAT.ChargeBal,0) + ISNULL(MAT.DebtBal,0) + ISNULL(MAT.OutlayBal,0)) >(SELECT ISNULL(KeyValue, 0)
FROM Settings
WHERE keyname = 'LockupUpValue')
AND MAT.Closed <> 'Y'
ELSE IF @Function='MONEYLANDERINGCHECKS'
-- Pino 2015-06-25 Rewrite START
SELECT RTRIM(ISNULL(CON.[Code], '')) AS [ClientCode],
RTRIM(ISNULL(CON.[Name], '')) AS [Name],
[dbo].[ky_RemoveSpuriousWhitespace2](RTRIM(ISNULL(CON.[Address], '')), 200) AS [Address],
RM.[Code] AS [Code],
RM.[Description] AS [Description]
FROM [dbo].[Contacts] CON
-- Get code and description of most recently started matter for this client
-- Because it's a Cross Apply it works akin to an INNER JOIN so clients with
-- no open matters are automatically excluded.
CROSS APPLY ( SELECT TOP 1
RTRIM(ISNULL(MAT.[Code], '')) AS [Code],
RTRIM(ISNULL(MAT.[Description], '')) AS [Description]
FROM [dbo].[matters] MAT
WHERE MAT.[ClientCode] = CON.[Code]
AND MAT.[Closed] <> 'Y'
ORDER BY MAT.[Started] DESC) RM
WHERE CON.[FE] = @Handler
AND ISNULL(CON.[Approved], 0) <> 1
-- Pino 2015-06-25 Rewrite END
ELSE IF @Function='NOESTIMATEGIVEN'
SELECT RTRIM(ISNULL(MAT.[Code], '')) AS [Code],
RTRIM(ISNULL(CON.[Name], '')) AS [Name],
RTRIM(ISNULL(MAT.[Description], '')) AS [Description]
FROM [dbo].[matters] MAT
INNER JOIN [dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
WHERE MAT.[FECode] = @Handler
AND ISNULL(MAT.[Section68], '') <> 'Y'
AND CASE WHEN isnumeric(MAT.[matter]) = 0
THEN 1
WHEN convert(int, MAT.[matter]) = 0
THEN 1
ELSE 0 END = 0
AND MAT.[Closed] <> 'Y'
ELSE IF @Function='WIP'
-- Pino 2015-05-26 - Please make sure that this calculation exactly mirrors the corresponding one
-- in [dbo].[Ky_NETDashBoard] - if either is changed, change the other one
-- accordingly
BEGIN
IF [dbo].[ISSAM4]() = 0
BEGIN
SELECT WP.[Matter] AS [Code],
RTRIM(ISNULL(CON.[NAME], '')) AS [ClientName],
RTRIM(ISNULL(CON.[CODE], '')) AS [ClientCode],
RTRIM(ISNULL(MT2.[Description], '')) AS [Description],
WP.[WIP] AS [WIP],
WP.[LastTimeEntryDate] AS [LastTimeEntryDate]
FROM ( SELECT TIE.[Matter],
ISNULL(SUM(ISNULL(TIE.[Charge], 0) - ISNULL(TIE.[BilledAmount], 0)), 0) AS [WIP],
MAX(TIE.[Date]) AS [LastTimeEntryDate]
FROM [dbo].[TimeEntry] TIE
INNER JOIN [dbo].[matters] MAT
ON MAT.[Code] = TIE.[Matter]
AND MAT.[Closed] = 'N'
WHERE TIE.[FeeEarn] = @Handler
AND TIE.[Rec_Irr] <> 'N'
AND TIE.[Matter] <> '~'
AND ( TIE.[TimeOrCharge] = 'T'
OR TIE.[TimeOrCharge] = 'C')
GROUP BY TIE.[Matter]) WP
INNER JOIN [dbo].[matters] MT2
INNER JOIN [dbo].[Contacts] CON
ON CON.[Code] = MT2.[ClientCode]
ON MT2.[Code] = WP.[Matter]
WHERE WP.[WIP] <> 0
END
ELSE
BEGIN
SELECT WP.[Matter] AS [Code],
RTRIM(ISNULL(CON.[NAME], '')) AS [ClientName],
RTRIM(ISNULL(CON.[CODE], '')) AS [ClientCode],
RTRIM(ISNULL(MT2.[Description], '')) AS [Description],
WP.[WIP] AS [WIP],
WP.[LastTimeEntryDate] AS [LastTimeEntryDate]
FROM ( SELECT TIE.[Matter],
ISNULL(SUM(CASE WHEN TIE.[InvoiceNo] = 0
THEN ISNULL(TIE.[Charge], 0)
ELSE 0 END), 0) AS [WIP],
MAX(TIE.[Date]) AS [LastTimeEntryDate]
FROM [dbo].[TimeEntry] TIE
INNER JOIN [dbo].[matters] MAT
ON MAT.[Code] = TIE.[Matter]
AND MAT.[Closed] = 'N'
WHERE TIE.[FeeEarn] = @Handler
AND TIE.[Rec_Irr] <> 'N'
AND TIE.[Matter] <> '~'
AND ( TIE.[TimeOrCharge] = 'T'
OR TIE.[TimeOrCharge] = 'C')
GROUP BY TIE.[Matter]) WP
INNER JOIN [dbo].[matters] MT2
INNER JOIN [dbo].[Contacts] CON
ON CON.[Code] = MT2.[ClientCode]
ON MT2.[Code] = WP.[Matter]
WHERE WP.[WIP] <> 0
END
END
ELSE IF @Function='OSINVOICE'
-- Pino 2015-05-26 - Please make sure that this calculation exactly mirrors the corresponding one
-- in [dbo].[Ky_NETDashBoard] - if either is changed, change the other one
-- accordingly
BEGIN
IF [dbo].[ISSAM4]() = 0
BEGIN
SET @NCOMMAND = N'
SELECT RTRIM(ISNULL(DLG.[Ref], '''')) AS [InvoiceRef],
RTRIM(ISNULL(DLG.[MATTER], '''')) AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [ClientName],
RTRIM(ISNULL(MAT.[Description],'''')) AS [Description],
ISNULL(CONVERT(VARCHAR,DLG.[DATE],106),'''') AS [InvoiceDate],
CONVERT(DECIMAL(19, 2), DLG.OSVALUE) AS [Outstanding],
DATEDIFF(dd, DLG.[DATE] ,GETDATE()) AS [DaysOld]
FROM [dbo].[matters] MAT
INNER JOIN [dbo].[DebtorsLedger] DLG
ON DLG.[MATTER] = MAT.[Code]
AND DLG.[OSVALUE] > 0
INNER JOIN [dbo].[Contacts] CON
ON CON.[Code] = MAT.[ClientCode]
WHERE MAT.[FECode] = @Handler'
END
ELSE
BEGIN
SET @NCOMMAND = N'
SELECT TRN.[OutlayBill] AS [InvoiceRef],
TRN.[MatterCode] AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [ClientName],
RTRIM(ISNULL(MAT.[Description], '''')) AS [Description],
CONVERT(VARCHAR, TRN.[TransDate], 106) AS [InvoiceDate],
CONVERT(DECIMAL(19, 2), ISNULL(TRN.[DebtorsValueOS], 0)) AS [Outstanding],
CASE WHEN TRN.[TransDate] IS NULL
THEN 0
WHEN TRN.[TransDate] > GETDATE()
THEN 0
WHEN TRN.[DebtorsValueOS] = 0
THEN 0
ELSE DATEDIFF(Day, TRN.[TransDate], GetDate()) END AS [DaysOld]
FROM [dbo].[Transactions] TRN
LEFT OUTER JOIN [dbo].[matters] MAT
INNER JOIN [dbo].[contacts] CON
ON CON.[Code] = MAT.[ClientCode]
ON MAT.[code] = TRN.[MatterCode]
WHERE TRN.[Posted] = ''Y''
AND TRN.[FeeEarner] = @Handler
AND TRN.[CorrectionInd] = ''L''
AND ( ( TRN.[XnType] = ''I''
AND TRN.[RecType] = ''H'')
OR ( TRN.[XnType] = ''J''
AND TRN.[RecType] = ''D''))
AND TRN.[DebtorsValueOS] > 0'
END
EXECUTE sp_executesql @NCOMMAND, N'@Handler VARCHAR(10)', @Handler = @Handler
END
ELSE IF @Function='UNDERTAKINGS'
SELECT
RTRIM(ISNULL(UND.MATTER,'')) AS Code,
RTRIM(ISNULL(Contacts.Name,'')) AS ClientName,
RTRIM(ISNULL(M.[Description],'')) AS [Description],
CASE
WHEN UND.UNDDATE IS NULL THEN ''
WHEN CONVERT(VARCHAR(11),UND.UNDDATE,112)='19000101' THEN ''
ELSE CONVERT(VARCHAR(11),UND.UNDDATE ,103)
END AS [Date]
FROM
dbo.[Undertakings] UND
LEFT JOIN
Matters AS M
ON
UND.MATTER = M.Code
LEFT JOIN
Contacts
ON
M.ClientCode = Contacts.Code
WHERE
UND.[AuthorisedByFE] = @Handler
AND
UND.[DISCHARGEDATE] IS NULL
AND M.[Closed] <> 'Y'
ELSE IF @Function='STATUTEDATE'
SELECT
RTRIM(ISNULL(M.Code,'')) AS Code,
RTRIM(ISNULL(Contacts.Name,'')) AS ClientName,
RTRIM(ISNULL(M.[Description],'')) AS [Description],
CASE
WHEN M.StatuteLimits IS NULL THEN ''
WHEN CONVERT(VARCHAR(11),M.StatuteLimits,112)='19000101' THEN ''
ELSE CONVERT(VARCHAR(11),M.StatuteLimits ,103)
END AS [Date]
FROM
Matters AS M
INNER JOIN
Contacts
ON
M.ClientCode = Contacts.Code
WHERE
M.FECode = @Handler
AND
DATEDIFF(DD, GetDate(), CASE WHEN ISNULL(M.[StatuteLimits], '21000101')='19000101' THEN '21000101' ELSE M.[StatuteLimits] END) <= 60
AND M.[Closed] <> 'Y'
ELSE IF @Function='OUTLAYBALANCE'
BEGIN
EXEC [dbo].[ky_NETOutlayBalanceDrilldown] @Handler
END
ELSE IF @Function='Fees Issued Current Month'
BEGIN
-- Rewrite by John START
-- KEYD-3505
IF [dbo].[ISSAM4]() = 0
BEGIN
SET @NCOMMAND = '
SELECT RTRIM(ISNULL(MAT.[Code], '''')) AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [Client],
RTRIM(ISNULL(MAT.[Description], '''')) AS [Matter],
ISNULL(BAD.[VALUE], 0) *
CASE WHEN ISNULL(BAH.[INVCR], '''') = ''C''
THEN -1
ELSE 1 END AS [Fee],
RTRIM(ISNULL(BAD.[REF], '''')) AS [Ref],
RTRIM(ISNULL(BAD.[NARR], '''')) AS [Narrative],
BAD.[DATE] AS [Date]
FROM [dbo].[BatchDetails] AS BAD
INNER JOIN [dbo].[BatchH] AS BAH
ON BAD.[BATCHNO] = BAH.[BATCHNO]
AND BAH.[Posted] = ''Y''
INNER JOIN [dbo].[KPIParameters] AS KPI
ON KPI.[Year] = BAD.[YEAR]
AND KPI.[Period] = BAD.[PERNO]
INNER JOIN [dbo].[matters] AS MAT
ON BAH.[MATTER] = MAT.[Code]
INNER JOIN [dbo].[Contacts] AS CON
ON MAT.[ClientCode] = CON.[Code]
WHERE ISNULL(BAD.[FEE], '''') = ''' + @HANDLER + '''
AND ISNULL(BAD.[TYPE], '''') = ''I''
AND ISNULL(BAD.[OUTLAY], '''') = ''F'''
END
ELSE
BEGIN
SET @NCOMMAND = '
SELECT RTRIM(ISNULL(MAT.[Code], '''')) AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [Name],
RTRIM(ISNULL(MAT.[Description], '''')) AS [Matter],
ISNULL(TRN.[FeesBilled], 0) *
ISNULL(TRN.[DrCr], 1) * -1 AS [Fee],
RTRIM(ISNULL(TRN.[TransRef], '''')) AS [Ref],
RTRIM(ISNULL(TRN.[Narrative], '''')) AS [Narrative],
TRN.[TransDate] AS [Date]
FROM [dbo].[Contacts] AS CON
INNER JOIN [dbo].[matters] AS MAT
ON CON.[Code] = MAT.[ClientCode]
INNER JOIN [dbo].[Transactions] AS HED
ON HED.[MatterCode] = MAT.[Code]
INNER JOIN [dbo].[Transactions] AS TRN
ON TRN.[BatchNo] = HED.[BatchNo]
AND TRN.[RecType] = ''D''
AND TRN.[LineType] = ''F''
AND TRN.[XnType] = ''I''
AND TRN.[Posted] = ''Y''
AND TRN.[CorrectionInd] IN (''L'', ''X'')
AND TRN.[FeeEarner] = ''' + @Handler + '''
INNER JOIN [dbo].[KPIParameters] AS KPI
ON KPI.[Year] = HED.[PostingYear]
AND KPI.[Period] = HED.[PostingPeriod]
WHERE ISNULL(HED.[RecType], '''') = ''H'''
END
EXEC (@NCOMMAND)
END
ELSE IF @Function='Fees Issued Last Month'
BEGIN
IF [dbo].[ISSAM4]() = 0
BEGIN
SET @NCOMMAND = '
SELECT RTRIM(ISNULL(MAT.[Code], '''')) AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [Client],
RTRIM(ISNULL(MAT.[Description], '''')) AS [Matter],
ISNULL(BAD.[VALUE], 0) *
CASE WHEN ISNULL(BAH.[INVCR], '''') = ''C''
THEN -1
ELSE 1 END AS [Fee],
RTRIM(ISNULL(BAD.[REF], '''')) AS [Ref],
RTRIM(ISNULL(BAD.[NARR], '''')) AS [Narrative],
BAD.[DATE] AS [Date]
FROM [dbo].[BatchDetails] AS BAD
INNER JOIN [dbo].[BatchH] AS BAH
ON BAD.[BATCHNO] = BAH.[BATCHNO]
AND BAH.[Posted] = ''Y''
INNER JOIN [dbo].[KPIParameters] AS KPI
ON (KPI.[Year] = BAD.[YEAR]
AND KPI.[Period] <> 1
AND KPI.[Period] - 1 = BAD.[PERNO])
OR (KPI.[Year] = BAD.[YEAR] - 1
AND KPI.[Period] = 1
AND BAD.[PERNO] = 12)
INNER JOIN [dbo].[matters] AS MAT
ON BAH.[MATTER] = MAT.[Code]
INNER JOIN [dbo].[Contacts] AS CON
ON MAT.[ClientCode] = CON.[Code]
WHERE ISNULL(BAD.[FEE], '''') = ''' + @HANDLER + '''
AND ISNULL(BAD.[TYPE], '''') = ''I''
AND ISNULL(BAD.[OUTLAY], '''') = ''F'''
END
ELSE
BEGIN
SET @NCOMMAND = '
SELECT RTRIM(ISNULL(MAT.[Code], '''')) AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [Name],
RTRIM(ISNULL(MAT.[Description], '''')) AS [Matter],
ISNULL(TRN.[FeesBilled], 0) *
ISNULL(TRN.[DrCr], 1) * - 1 AS [Fee],
RTRIM(ISNULL(TRN.[TransRef], '''')) AS [Ref],
RTRIM(ISNULL(TRN.[Narrative], '''')) AS [Narrative],
TRN.[TransDate] AS [Date]
FROM [dbo].[Contacts] AS CON
INNER JOIN [dbo].[matters] AS MAT
ON CON.[Code] = MAT.[ClientCode]
INNER JOIN [dbo].[Transactions] AS HED
ON HED.[MatterCode] = MAT.[Code]
INNER JOIN [dbo].[Transactions] AS TRN
ON TRN.[BatchNo] = HED.[BatchNo]
AND TRN.[RecType] = ''D''
AND TRN.[LineType] = ''F''
AND TRN.[XnType] = ''I''
AND TRN.[Posted] = ''Y''
AND TRN.[CorrectionInd] IN (''L'', ''X'')
AND TRN.[FeeEarner] = ''' + @HANDLER + '''
INNER JOIN [dbo].[KPIParameters] AS KPI
ON (KPI.[Year] = HED.[PostingYear]
AND KPI.[Period] <> 1
AND KPI.[Period] - 1 = HED.[PostingPeriod])
OR (KPI.[Year] = HED.[PostingYear] - 1
AND KPI.[Period] = 1
AND HED.[PostingYear] = 12)
WHERE ISNULL(HED.[RecType], '''') = ''H'''
END
EXEC (@NCOMMAND)
END
ELSE IF @Function='Fees Issued Year to Date'
BEGIN
IF [dbo].[ISSAM4]() = 0
BEGIN
SET @NCOMMAND = '
SELECT RTRIM(ISNULL(MAT.[Code], '''')) AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [Client],
RTRIM(ISNULL(MAT.[Description], '''')) AS [Matter],
CASE WHEN ISNULL(BAH.[INVCR], '''') = ''C'' THEN -BAD.[VALUE]
WHEN ISNULL(BAH.[INVCR], '''') = ''I'' THEN BAD.[VALUE]
ELSE 0 END AS [Fee],
RTRIM(ISNULL(BAD.[REF], '''')) AS [Ref],
RTRIM(ISNULL(BAD.[NARR], '''')) AS [Narrative],
BAD.[DATE] AS [Date]
FROM [dbo].[BatchDetails] AS BAD
INNER JOIN [dbo].[BatchH] AS BAH
ON BAD.[BATCHNO] = BAH.[BATCHNO]
AND BAH.[Posted] = ''Y''
INNER JOIN [dbo].[KPIParameters] AS KPI
ON KPI.[Year] = BAD.[YEAR]
AND KPI.[Period] >= BAD.[PERNO]
INNER JOIN [dbo].[matters] AS MAT
ON BAH.[MATTER] = MAT.[Code]
INNER JOIN [dbo].[Contacts] AS CON
ON MAT.[ClientCode] = CON.[Code]
WHERE ISNULL(BAD.[FEE], '''') = ''' + @HANDLER + '''
AND ISNULL(BAD.[TYPE], '''') = ''I''
AND ISNULL(BAD.[OUTLAY], '''') = ''F'''
END
ELSE
BEGIN
SET @NCOMMAND = '
SELECT RTRIM(ISNULL(MAT.[Code], '''')) AS [Code],
RTRIM(ISNULL(CON.[Name], '''')) AS [Name],
RTRIM(ISNULL(MAT.[Description], '''')) AS [Matter],
ISNULL(TRN.[FeesBilled], 0) *
ISNULL(TRN.[DrCr], 1) * - 1 AS [Fee],
RTRIM(ISNULL(TRN.[TransRef], '''')) AS [Ref],
RTRIM(ISNULL(TRN.[Narrative], '''')) AS [Narrative],
TRN.[TransDate] AS [Date]
FROM [dbo].[Contacts] AS CON
INNER JOIN [dbo].[matters] AS MAT
ON CON.[Code] = MAT.[ClientCode]
INNER JOIN [dbo].[Transactions] AS HED
ON MAT.[Code] = HED.[MatterCode]
INNER JOIN [dbo].[Transactions] AS TRN
ON TRN.[BatchNo] = HED.[BatchNo]
AND TRN.[RecType] = ''D''
AND TRN.[LineType] = ''F''
AND TRN.[XnType] = ''I''
AND TRN.[Posted] = ''Y''
AND (TRN.[CorrectionInd] = ''L''
OR TRN.[CorrectionInd] = ''X'')
AND TRN.[FeeEarner] = ''' + @Handler + '''
INNER JOIN [dbo].[KPIParameters] AS KPI
ON KPI.[Year] = HED.[PostingYear]
AND KPI.[Period] >= HED.[PostingPeriod]
WHERE HED.[RecType] = ''H'''
END
EXEC (@NCOMMAND)
END
-- Rewrite by John END
ELSE IF @Function='TimeDayBook'
BEGIN
SELECT
ISNULL(TDB.Matter,'') AS Code
,RTRIM(ISNULL(CON.Name,'')) AS Client
,RTRIM(ISNULL(MAT.[Description],'')) AS 'Matter Description'
,ISNULL(TDB.[Date],'') AS [Date]
,RTRIM(ISNULL(TDB.Comment,'')) AS Comment
,ISNULL(TDB.[Time],0) AS [Time]
,ISNULL(TDB.Charge,0) AS Charge
FROM
[dbo].[TimeDayBook] TDB
INNER JOIN
matters MAT
INNER JOIN
contacts CON
ON
MAT.ClientCode =CON.Code
ON
TDB.Matter =MAT.Code
WHERE
TDB.[FEEEARN] = @Handler
AND
TDB.[TIMEORCHARGE] = 'T'
END
ELSE IF @Function='TimeDayBookforWeek'
BEGIN
SET @GETDATE = GetDate()
SELECT @TODAYNUM = DATEPART(WEEKDAY, @GETDATE) - DATEPART(WEEKDAY, '20000102') --the 2nd of January, 2000 was a Monday
IF @TODAYNUM <= 0
SET @TODAYNUM = @TODAYNUM + 7
SET @MONDAY = CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY, 1 - @TODAYNUM, @GETDATE), 112))
SELECT
ISNULL(TIE.Matter,'') AS Code
,RTRIM(ISNULL(CON.Name,'')) AS Client
,RTRIM(ISNULL(MAT.[Description],'')) AS 'Matter Description'
,ISNULL(TIE.[Date],'') AS [Date]
,RTRIM(ISNULL(TIE.Comment,'')) AS Comment
,ISNULL(TIE.[Time],0) AS [Time]
,ISNULL(TIE.Charge,0) AS Charge
FROM
[TimeEntry] TIE
INNER JOIN
matters MAT
INNER JOIN
contacts CON
ON
MAT.ClientCode =CON.Code
ON
TIE.Matter =MAT.Code
WHERE
TIE.[FEEEARN] = @Handler
AND
TIE.[TIMEORCHARGE] = 'T'
AND
TIE.[Date] >= @MONDAY
AND
TIE.[DATE] <= GETDATE()
AND
ISNULL(TIE.[TASK], '') <> 'WRI'
END
ELSE IF @Function='TimeDayBookforMonth'
BEGIN
SET @GETDATE = GetDate()
SELECT @TODAYNUM = DATEPART(DAY, @GETDATE)
SET @STARTOFMONTH = CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY, 1 - @TODAYNUM, @GETDATE), 112))
SELECT
ISNULL(TIE.Matter,'') AS Code
,RTRIM(ISNULL(CON.Name,'')) AS Client
,RTRIM(ISNULL(MAT.[Description],'')) AS 'Matter Description'
,ISNULL(TIE.[Date],'') AS [Date]
,RTRIM(ISNULL(TIE.Comment,'')) AS Comment
,ISNULL(TIE.[Time],0) AS [Time]
,ISNULL(TIE.Charge,0) AS Charge
FROM
[TimeEntry] TIE
INNER JOIN
matters MAT
INNER JOIN
contacts CON
ON
MAT.ClientCode =CON.Code
ON
TIE.Matter =MAT.Code
WHERE
TIE.[FEEEARN] = @Handler
AND
TIE.[TIMEORCHARGE] = 'T'
AND
TIE.[Date] >= @STARTOFMONTH
AND
TIE.[DATE] <= GETDATE()
AND
ISNULL(TIE.[TASK], '') <> 'WRI'
END
ELSE IF @Function='TimeDayBookforYear'
BEGIN
SELECT @STARTOFYEAR = KPP.[StartOfYear]
FROM [dbo].[KPIParameters] KPP
SET @STARTOFYEAR = ISNULL(@STARTOFYEAR, CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GetDate())) + '0101'))
SELECT
ISNULL(TIE.Matter,'') AS Code
,RTRIM(ISNULL(CON.Name,'')) AS Client
,RTRIM(ISNULL(MAT.[Description],'')) AS 'Matter Description'
,ISNULL(TIE.[Date],'') AS [Date]
,RTRIM(ISNULL(TIE.Comment,'')) AS Comment
,ISNULL(TIE.[Time],0) AS [Time]
,ISNULL(TIE.Charge,0) AS Charge
FROM
[TimeEntry] TIE
INNER JOIN
matters MAT
INNER JOIN
contacts CON
ON
MAT.ClientCode =CON.Code
ON
TIE.Matter =MAT.Code
WHERE TIE.[FEEEARN] = @Handler
AND TIE.[TIMEORCHARGE] = 'T'
AND TIE.[Date] >= @STARTOFYEAR
AND TIE.[DATE] <= GETDATE()
AND ISNULL(TIE.[TASK], '') <> 'WRI'
END
ELSE IF @Function='AdminTimeDayBookforWeek'
BEGIN
SET @GETDATE = GetDate()
SELECT @TODAYNUM = DATEPART(WEEKDAY, @GETDATE) - DATEPART(WEEKDAY, '20000102') --the 2nd of January, 2000 was a Monday
IF @TODAYNUM <= 0
SET @TODAYNUM = @TODAYNUM + 7
SET @MONDAY = CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY, 1 - @TODAYNUM, @GETDATE), 112))
SELECT
ISNULL(TIE.Matter,'') AS Code
,ISNULL(TIE.[Date],'') AS [Date]
,RTRIM(ISNULL(TIE.Comment,'')) AS Comment
,ISNULL(TIE.[Time],0) AS [Time]
,ISNULL(TIE.Charge,0) AS Charge
FROM
[TimeEntry] TIE
WHERE
TIE.[FEEEARN] = @Handler
AND
TIE.[TIMEORCHARGE] = 'T'
AND
TIE.[Date] >= @MONDAY
AND
TIE.[DATE] <= GETDATE()
AND
ISNULL(TIE.[TASK], '') <> 'WRI'
AND
TIE.Matter ='~'
END
ELSE IF @Function='AdminTimeDayBookforMonth'
BEGIN
SET @GETDATE = GetDate()
SELECT @TODAYNUM = DATEPART(DAY, @GETDATE)
SET @STARTOFMONTH = CONVERT(DATETIME, CONVERT(VARCHAR, DATEADD(DAY, 1 - @TODAYNUM, @GETDATE), 112))
SELECT
ISNULL(TIE.Matter,'') AS Code
,ISNULL(TIE.[Date],'') AS [Date]
,RTRIM(ISNULL(TIE.Comment,'')) AS Comment
,ISNULL(TIE.[Time],0) AS [Time]
,ISNULL(TIE.Charge,0) AS Charge
FROM
[TimeEntry] TIE
WHERE
TIE.[FEEEARN] = @Handler
AND
TIE.[TIMEORCHARGE] = 'T'
AND
TIE.[Date] >= @STARTOFMONTH
AND
TIE.[DATE] <= GETDATE()
AND
ISNULL(TIE.[TASK], '') <> 'WRI'
AND
TIE.Matter ='~'
END
ELSE IF @Function='AdminTimeDayBookforYear'
BEGIN
SELECT @STARTOFYEAR = KPP.[StartOfYear]
FROM [dbo].[KPIParameters] KPP
SET @STARTOFYEAR = ISNULL(@STARTOFYEAR, CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GetDate())) + '0101'))
SELECT
ISNULL(TIE.Matter,'') AS Code
,ISNULL(TIE.[Date],'') AS [Date]
,RTRIM(ISNULL(TIE.Comment,'')) AS Comment
,ISNULL(TIE.[Time],0) AS [Time]
,ISNULL(TIE.Charge,0) AS Charge
FROM
[TimeEntry] TIE
WHERE
TIE.[FEEEARN] = @Handler
AND
TIE.[TIMEORCHARGE] = 'T'
AND
TIE.[Date] >= @STARTOFYEAR
AND
TIE.[DATE] <= GETDATE()
AND
ISNULL(TIE.[TASK], '') <> 'WRI'
AND
TIE.Matter ='~'
END
ELSE IF @Function='OSFees'
BEGIN
IF [dbo].[ISSAM4]() = 0
BEGIN
SET @NCOMMAND = N'
SELECT Code, Description, DTL.OSFEES AS [OSFees]
FROM [dbo].[matters] MAT
INNER JOIN [dbo].[DebtorsLedger] DTL
ON DTL.[MATTER] = MAT.[Code]
AND DTL.[OSValue] > 0
WHERE MAT.[FECode] = @Handler'
END
ELSE
BEGIN
SET @NCOMMAND = N'
SELECT
Code,
Description,
ISNULL(TRN.[FeesOS], 0) AS [OSFees]
FROM [dbo].[Transactions] TRN
WHERE TRN.[Posted] = ''Y''
AND TRN.[FeeEarner] = ''ADM''
AND TRN.[CorrectionInd] = ''L''
AND ( ( TRN.[XnType] = ''I''
AND TRN.[RecType] = ''H'')
OR ( TRN.[XnType] = ''J''
AND TRN.[RecType] = ''D''))
AND TRN.[DebtorsValueOS] > 0
'
END
EXECUTE sp_executesql @NCOMMAND, N'@Handler VARCHAR(10)', @Handler = @Handler
END
ELSE IF @Function='DebtorDays'
BEGIN
IF [dbo].[ISSAM4]() = 0
BEGIN
SET @NCOMMAND = N'
SELECT Code,Description, DTL.DATE As TransDate,
ISNULL(CASE WHEN DTL.[DATE] IS NULL
THEN 0
WHEN DTL.[DATE] > GETDATE()
THEN 0
WHEN DTL.[OSVALUE] = 0
THEN 0
ELSE DATEDIFF(Day, DTL.[DATE], GetDate()) END, 0) AS [DebtorDays],
ISNULL(CASE WHEN DTL.[DATE] IS NULL
THEN 0
WHEN DTL.[DATE] > GETDATE()
THEN 0
WHEN DTL.[OSVALUE] = 0
THEN 0
ELSE 1 END, 0) AS [DebtorEntries]
FROM [dbo].[matters] MAT
INNER JOIN [dbo].[DebtorsLedger] DTL
ON DTL.[MATTER] = MAT.[Code]
AND DTL.[OSValue] > 0
WHERE MAT.[FECode] = @Handler'
END
ELSE
BEGIN
SET @NCOMMAND = N'
SELECT
Code,[Description], TRN.[TransDate] AS TransDate,
CASE WHEN TRN.[TransDate] IS NULL
THEN 0
WHEN TRN.[TransDate] > GETDATE()
THEN 0
WHEN TRN.[DebtorsValueOS] = 0
THEN 0
ELSE DATEDIFF(Day, TRN.[TransDate], GetDate()) END) AS [DebtorDays],
CASE WHEN TRN.[TransDate] IS NULL
THEN 0
WHEN TRN.[TransDate] > GETDATE()
THEN 0
WHEN TRN.[DebtorsValueOS] = 0
THEN 0
ELSE 1 END) AS [DebtorEntries]
FROM [dbo].[Transactions] TRN
WHERE TRN.[Posted] = ''Y''
AND TRN.[FeeEarner] = ''ADM''
AND TRN.[CorrectionInd] = ''L''
AND ( ( TRN.[XnType] = ''I''
AND TRN.[RecType] = ''H'')
OR ( TRN.[XnType] = ''J''
AND TRN.[RecType] = ''D''))
AND TRN.[DebtorsValueOS] > 0
'
END
EXECUTE sp_executesql @NCOMMAND, N'@Handler VARCHAR(10)', @Handler = @Handler
END
SET NOCOUNT OFF
END
GO
INSERT
INTO [dbo].[Settings]
([KeyName],
[KeyValue])
SELECT [NW].[KeyName],
[NW].[KeyValue]
FROM (SELECT 'MakeFileReadonlyWhenComplete' AS [KeyName],
'0' AS [KeyValue]) [NW]
LEFT OUTER JOIN [dbo].[Settings] [SET]
ON [SET].[KeyName] = [NW].[KeyName]
WHERE [SET].[KeyName] IS NULL
GO
INSERT
INTO [dbo].[Settings]
([KeyName],
[KeyValue])
SELECT [NW].[KeyName],
[NW].[KeyValue]
FROM (SELECT 'CompositeBilling' AS [KeyName],
'N' AS [KeyValue]) [NW]
LEFT OUTER JOIN [dbo].[Settings] [SET]
ON [SET].[KeyName] = [NW].[KeyName]
WHERE [SET].[KeyName] IS NULL
GO
INSERT
INTO [dbo].[Settings]
([KeyName],
[KeyValue])
SELECT [NW].[KeyName],
[NW].[KeyValue]
FROM (SELECT 'EmailEnableSSL' AS [KeyName],
'0' AS [KeyValue]) [NW]
LEFT OUTER JOIN [dbo].[Settings] [SET]
ON [SET].[KeyName] = [NW].[KeyName]
WHERE [SET].[KeyName] IS NULL
GO
INSERT
INTO [dbo].[Settings]
([KeyName],
[KeyValue])
SELECT [NW].[KeyName],
[NW].[KeyValue]
FROM (SELECT 'EmailUseDefaultCredentials' AS [KeyName],
'0' AS [KeyValue]) [NW]
LEFT OUTER JOIN [dbo].[Settings] [SET]
ON [SET].[KeyName] = [NW].[KeyName]
WHERE [SET].[KeyName] IS NULL
GO