Cursors

--Step#1 and Step#2
-- The results are stored into a temporary table in PVDB, ProjectOID_Temp.
-- Truncate Table ProjectOID_Temp  before running this step to start afresh.

DEClARE @ProjectOID as nvarchar (100)
DECLARE @TaskOID as nvarchar (100)

DECLARE ProjectOID_Cursor CURSOR FOR
SELECT  OID as projectOID
from    project
WHERE   ( state = 3 )
        AND ( status = 1 )
        AND ( trackETC = 1 )
OPEN ProjectOID_Cursor
FETCH NEXT FROM ProjectOID_Cursor INTO @ProjectOID
WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT ProjectOID_Temp (ProjectOID, TaskOID)
    SELECT  projectOID,
        OID AS taskOID
    FROM    projecttask
    WHERE   projectOID = @projectOID
        AND trackTime = 1
        AND parentTaskOID IS NOT NULL

FETCH NEXT FROM ProjectOID_Cursor
INTO @ProjectOID
END
CLOSE ProjectOID_Cursor
DEALLOCATE ProjectOID_Cursor

--- Step#3
-- Run this script in a different window.
-- The results are stored into a table ProjectOID_Temp2. Truncate this table to start once again.
DEClARE @ProjectOID as nvarchar (100)
DECLARE @TaskOID as nvarchar (100)
DECLARE @retTable TABLE (
  TaskOID nvarchar(100),
  TaskResourceOID nvarchar(100),
  EvUserOID nvarchar(100)
)
DECLARE TaskOID_Cursor CURSOR FOR
SELECT  TaskOID FROM ProjectOID_Temp
OPEN TaskOID_Cursor
FETCH NEXT FROM TaskOID_Cursor INTO @TaskOID
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @retTable
      SELECT @TaskOID, t1.OID as taskResourceOID, t2.OID as EvUserOID
    FROM (select OID, resourceOID from taskresourcehours
    where projecttaskoid =   @TaskOID) as t1,
    (
     select OID, workerOID
     from Evuser
     where workerOID in
      ( select resourceOID from taskresourcehours where projecttaskoid = @TaskOID )
    ) as t2
    WHERE t1.resourceOID = t2.workerOID
FETCH NEXT FROM TaskOID_Cursor
INTO @TaskOID
END
CLOSE TaskOID_Cursor
DEALLOCATE TaskOID_Cursor
INSERT INTO ProjectOID_Temp2
SELECT * FROM @retTable

--- Step#4 stop Primavera service

-- Step#5 Final Update. Backup the database before running this.
-- Run this in a separate query explorer window.

--DECLARE @ProjectOID as nvarchar (100)
DECLARE @TaskOID as nvarchar (100)
DECLARE @TaskResourceOID as nvarchar (100)
DECLARE @EvResourceOID as nvarchar (100)

DECLARE UpdateOID_Cursor CURSOR FOR
SELECT TaskOID, TaskResourceOID, EvResourceOID FROM ProjectOID_Temp2
OPEN UpdateOID_Cursor
FETCH NEXT FROM UpdateOID_Cursor INTO @TaskOID, @TaskResourceOID, @EvResourceOID
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE  taskresourcehours
    SET    lActualHours = ( SELECT SUM(minutes)
                         FROM   timesheetcell
                         WHERE  timesheetrowOID in (
                                SELECT  OID
                                FROM    timesheetrow
                                WHERE   projecttaskOID = @TaskOID
                                        AND timereportoid in (
                                        SELECT  OID
                                        FROM    timereport
                                        WHERE   userOID = @EvResourceOID ) )
                       ) / 60 * 10000
    WHERE   OID = @TaskResourceOID
FETCH NEXT FROM UpdateOID_Cursor
INTO @TaskOID, @TaskResourceOID, @EvResourceOID
END
CLOSE UpdateOID_Cursor
DEALLOCATE UpdateOID_Cursor