CREATE PROCEDURE [dbo].[spClaritySPM]
@StartDateTime AS varchar(50) = '2000/01/01 00:00:00'
,@EndDateTime AS varchar(50) = '2100/01/01 23:59:59'
,@UserID AS varchar(50) = '0'
,@ProjectID AS varchar(50) = '0'
,@TaskID AS varchar(50) = '0'
,@GMTOffset AS smallInt = '0'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RunTime AS Int
SET @StartDateTime = CAST(@StartDateTime As DateTime)
SET @EndDateTime = CAST(@EndDateTime As DateTime)
SET @RunTime = datediff(second, {d '1970-01-01'}, GetutcDate())
UPDATE act_log
SET zClarityLastSync = @RunTime
WHERE act_log.analyst IN (SELECT contact_uuid FROM ca_contact WHERE userid = @UserID)
AND ((act_log.time_stamp + (@GMTOffset * 3600)) BETWEEN datediff(second, {d '1970-01-01'}, @StartDateTime) AND datediff(second, {d '1970-01-01'}, @EndDateTime))
AND zClarityLastSync IS NULL
AND id IN (SELECT act_log.id
FROM call_req INNER JOIN
prob_ctg ON call_req.category = prob_ctg.persid INNER JOIN
act_log ON call_req.persid = act_log.call_req_id
WHERE (prob_ctg.zcl_task_id = @TaskID))
UPDATE chgalg
SET zClarityLastSync = @RunTime
WHERE chgalg.analyst IN (SELECT contact_uuid FROM ca_contact WHERE userid = @UserID) AND ((chgalg.time_stamp + (@GMTOffset * 3600)) BETWEEN datediff(second, {d '1970-01-01'}, @StartDateTime) AND datediff(second, {d '1970-01-01'}, @EndDateTime))
AND zClarityLastSync IS NULL
AND ID IN (SELECT DISTINCT chgalg.id
FROM chg INNER JOIN
chgcat AS chgcat ON chg.category = chgcat.code INNER JOIN
chgalg AS chgalg ON chg.id = chgalg.change_id
WHERE (CASE WHEN chg.zcl_task_id IS NULL THEN chgcat.zcl_task_id ELSE chg.zcl_task_id END = @TaskID) AND (chgalg.zClarityLastSync IS NULL))
UPDATE wf
SET zClarityLastSync = @RunTime
WHERE assignee IN (SELECT contact_uuid FROM ca_contact WHERE userid = @UserID) AND ((last_mod_dt + (@GMTOffset * 3600)) BETWEEN datediff(second, {d '1970-01-01'}, @StartDateTime) AND datediff(second, {d '1970-01-01'}, @EndDateTime))
AND wf.id IN (SELECT DISTINCT wf.id
FROM chg INNER JOIN
chgcat AS chgcat ON chg.category = chgcat.code INNER JOIN
wf ON chg.id = wf.object_id
WHERE (wf.object_type = N'chg') AND (CASE WHEN chg.zcl_task_id IS NULL THEN chgcat.zcl_task_id ELSE chg.zcl_task_id END = @TaskID) AND
(wf.zClarityLastSync IS NULL) OR
(wf.object_type = N'chg') AND (CASE WHEN chg.zcl_task_id IS NULL THEN chgcat.zcl_task_id ELSE chg.zcl_task_id END = @TaskID) AND
ISNULL(wf.actual_duration,0) - ISNULL(wf.zClarityTime, 0) <> 0)
DECLARE @tblOutput TABLE
(
TotalTime decimal(10,4),
UserID varchar(50),
ProjID varchar(50),
TaskID varchar(50),
TransactionDate datetime
)
INSERT INTO @tblOutput (TotalTime, UserID, ProjID, TaskID, TransactionDate)
SELECT round(1.0000 * act_log.time_spent/3600,4) AS TotalTime, ca_contact.userid AS UserID, prob_ctg.zcl_proj_id AS ProjID, prob_ctg.zcl_task_id AS TaskID,
CONVERT([datetime], DATEADD(hh,@GMTOffset,(DATEADD(s, act_log.time_stamp, '19700101'))), 111) AS TransactionDate
FROM act_log, call_req, ca_contact, prob_ctg prob_ctg
WHERE act_log.call_req_id = call_req.persid AND act_log.analyst = ca_contact.contact_uuid AND call_req.category = prob_ctg.persid AND
act_log.zClarityLastSync = @RunTime
AND ca_contact.UserID = @UserID AND prob_ctg.zcl_proj_id = @ProjectID AND prob_ctg.zcl_task_id = @TaskID
AND round(1.0000 * act_log.time_spent/3600,4) <> 0
INSERT INTO @tblOutput (TotalTime, UserID, ProjID, TaskID, TransactionDate)
SELECT round(1.0000 * chgalg_1.time_spent/3600,4) AS TotalTime, ca_contact_1.userid AS UserID, CASE WHEN chg.zcl_proj_id IS NOT NULL
THEN chg.zcl_proj_id ELSE chgcat.zcl_proj_id END AS ProjID, CASE WHEN chg.zcl_task_id IS NOT NULL
THEN chg.zcl_task_id ELSE chgcat.zcl_task_id END AS TaskID,
CONVERT([datetime], DATEADD(hh,@GMTOffset,(DATEADD(s, chgalg_1.time_stamp, '19700101'))), 111)
AS TransactionDate
FROM chgalg chgalg_1, chg, chgcat, ca_contact ca_contact_1
WHERE chgalg_1.change_id = chg.id AND chg.category = chgcat.code AND chgalg_1.analyst = ca_contact_1.contact_uuid AND
chgalg_1.zClarityLastSync = @RunTime
AND ca_contact_1.UserID = @UserID AND CASE WHEN chg.zcl_proj_id IS NOT NULL
THEN chg.zcl_proj_id ELSE chgcat.zcl_proj_id END = @ProjectID AND CASE WHEN chg.zcl_task_id IS NOT NULL
THEN chg.zcl_task_id ELSE chgcat.zcl_task_id END = @TaskID
AND round(1.0000 * chgalg_1.time_spent/3600,4) <> 0
INSERT INTO @tblOutput (TotalTime, UserID, ProjID, TaskID, TransactionDate)
SELECT ROUND(1.0000 * (ISNULL(wf.actual_duration, 0) - ISNULL(wf.zClarityTime, 0)), 4) / 3600 AS TotalTime, ca_contact_2.userid AS UserID, CASE WHEN chg_1.zcl_proj_id IS NOT NULL
THEN chg_1.zcl_proj_id ELSE chgcat_1.zcl_proj_id END AS ProjID, CASE WHEN chg_1.zcl_task_id IS NOT NULL
THEN chg_1.zcl_task_id ELSE chgcat_1.zcl_task_id END AS TaskID,
CONVERT([datetime], DATEADD(hh,@GMTOffset,(DATEADD(s, wf.last_mod_dt, '19700101'))), 111)
AS TransactionDate
FROM chg chg_1, chgcat chgcat_1, wf, ca_contact ca_contact_2
WHERE chg_1.category = chgcat_1.code AND chg_1.id = wf.object_id AND wf.assignee = ca_contact_2.contact_uuid AND (wf.object_type = 'chg') AND
wf.zClarityLastSync = @RunTime
AND ca_contact_2.UserID = @UserID AND CASE WHEN chg_1.zcl_proj_id IS NOT NULL
THEN chg_1.zcl_proj_id ELSE chgcat_1.zcl_proj_id END = @ProjectID AND CASE WHEN chg_1.zcl_task_id IS NOT NULL
THEN chg_1.zcl_task_id ELSE chgcat_1.zcl_task_id END = @TaskID
AND (ROUND(1.0000 * (ISNULL(wf.actual_duration, 0) - ISNULL(wf.zClarityTime, 0)), 4) / 3600) <> 0
UPDATE wf
SET zClarityTime = actual_duration
WHERE assignee IN (SELECT contact_uuid FROM ca_contact WHERE userid = @UserID) AND ((last_mod_dt + (@GMTOffset * 3600)) BETWEEN datediff(second, {d '1970-01-01'}, @StartDateTime) AND datediff(second, {d '1970-01-01'}, @EndDateTime))
AND zClarityLastSync = @RunTime
SELECT round(1.0000 * (SUM(TotalTime)),4) AS TotalTime, convert(varchar(10),TransactionDate, 120) AS TransactionDate
FROM @tblOutput
GROUP BY convert(varchar(10),TransactionDate, 120)
END
GO
GRANT EXECUTE ON [dbo].[spClaritySPM] TO [ServiceDesk]
GO
[dbo].[spClaritySPM]