-- PROCEDURE: internal.pupdatereadingeggsweektot(smallint, date, integer)

-- DROP PROCEDURE IF EXISTS internal.pupdatereadingeggsweektot(smallint, date, integer);

CREATE OR REPLACE PROCEDURE internal.pupdatereadingeggsweektot(
	par_calcyear smallint DEFAULT NULL::smallint,
	par_calcdate date DEFAULT NULL::date,
	par_personid integer DEFAULT NULL::integer)
LANGUAGE 'plpgsql'
AS $BODY$

DECLARE
    var_ToTStartDate DATE DEFAULT dbo.fgetsystemparameter('Reading Eggs', 'Time on Task', 'Valid From', 0, '2018-01-01');
    var_FromDate DATE;
    var_ToDate DATE;
    var_rowcount int;
BEGIN
    /*
    [7810 - Severity CRITICAL - PostgreSQL doesn't support the SET NOCOUNT. If need try another way to send message back to the client application.]
    SET NOCOUNT ON;
    */
    IF par_CalcYear IS NOT NULL THEN
        CALL internal.pupdatereadingeggsweektotyear(par_CalcYear := par_CalcYear, par_PersonID := par_PersonID);
    ELSE
        IF par_CalcDate IS NOT NULL THEN
            CALL internal.pupdatereadingeggsweektotdate(par_CalcDate := par_CalcDate, par_PersonID := par_PersonID);
        END IF;
    END IF;
    /* IF @CalcDate IS NOT NULL */
    /* BEGIN */
    /* SET @FromDate = @CalcDate; */
    /* SET @ToDate	= @CalcDate; */
    /* END */
    /* ELSE IF @CalcYear IS NOT NULL */
    /* BEGIN */
    /* SET @FromDate = DATEFROMPARTS(@CalcYear,1,1); */
    /* SET @ToDate = DATEFROMPARTS(@CalcYear,12,31); */
    /* END */
    /* ELSE */
    /* BEGIN */
    /* SET @FromDate = @ToTStartDate; */
    /* SET @ToDate = GETDATE(); */
    /* END */
--     UPDATE dbo.learnerdatareadingeggs AS d
--     SET "re week time (sec)" = NULL, "rex week time (sec)" = NULL, "fp week time (sec)" = NULL
--         WHERE d.date >= var_ToTStartDate AND date_part('year', d.date) = par_CalcYear AND
--         /* AND D.[Date] BETWEEN @FromDate AND @ToDate; */
--         ((par_PersonID IS NULL) OR (par_PersonID IS NOT NULL AND d.personid = par_PersonID));
--     GET DIAGNOSTICS var_rowcount = ROW_COUNT;
-- --     RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Rows cleared: %d', var_rowcount USING ERRCODE = '50000';
--     WITH tt (personid, dateyear, date, "re time (sec)", "rex time (sec)", "fp time (sec)", seq)
--     AS (SELECT
--         d.personid, date_part('year', d.date) AS dateyear, d.date, d."re time (sec)", d."rex time (sec)", d."fp time (sec)", ROW_NUMBER() OVER (PARTITION BY date_part('year', d.date), d.personid ORDER BY d.date) AS seq
--         FROM dbo.learnerdatareadingeggs AS d
--         WHERE d.date >= var_ToTStartDate AND date_part('year', d.date) = par_CalcYear AND ((par_PersonID IS NULL) OR (par_PersonID IS NOT NULL AND d.personid = par_PersonID)))
--     /* AND D.[Date] BETWEEN DATEADD(day,-7,@FromDate) AND @ToDate */
--     , mm (personid, dateyear, date, removement, rexmovement, fpmovement, seq)
--     AS (SELECT
--         d.personid, d.dateyear, d.date,
--         CASE
--             WHEN d."re time (sec)" IS NOT NULL AND d1."re time (sec)" IS NULL THEN d."re time (sec)"
--             WHEN d."re time (sec)" IS NOT NULL AND d1."re time (sec)" IS NOT NULL AND d."re time (sec)" >= d1."re time (sec)" THEN d."re time (sec)" - d1."re time (sec)"
--             WHEN d."re time (sec)" IS NOT NULL AND d1."re time (sec)" IS NOT NULL AND d."re time (sec)" < d1."re time (sec)" THEN d."re time (sec)"
--         /* WHEN d.[re time (sec)] IS NOT NULL AND d1.[re time (sec)] IS NOT NULL											  THEN d.[re time (sec)] - d1.[re time (sec)] */
--             WHEN d."re time (sec)" IS NULL AND d1."re time (sec)" IS NOT NULL THEN NULL
--         END,
--         CASE
--             WHEN d."rex time (sec)" IS NOT NULL AND d1."rex time (sec)" IS NULL THEN d."rex time (sec)"
--             WHEN d."rex time (sec)" IS NOT NULL AND d1."rex time (sec)" IS NOT NULL AND d."rex time (sec)" >= d1."rex time (sec)" THEN d."rex time (sec)" - d1."rex time (sec)"
--             WHEN d."rex time (sec)" IS NOT NULL AND d1."rex time (sec)" IS NOT NULL AND d."rex time (sec)" < d1."rex time (sec)" THEN d."rex time (sec)"
--         /* WHEN d.[rex time (sec)] IS NOT NULL AND d1.[rex time (sec)] IS NOT NULL                                               THEN d.[rex time (sec)] - d1.[rex time (sec)] */
--             WHEN d."rex time (sec)" IS NULL AND d1."rex time (sec)" IS NOT NULL THEN NULL
--         END,
--         CASE
--             WHEN d."fp time (sec)" IS NOT NULL AND d1."fp time (sec)" IS NULL THEN d."fp time (sec)"
--             WHEN d."fp time (sec)" IS NOT NULL AND d1."fp time (sec)" IS NOT NULL AND d."fp time (sec)" >= d1."fp time (sec)" THEN d."fp time (sec)" - d1."fp time (sec)"
--             WHEN d."fp time (sec)" IS NOT NULL AND d1."fp time (sec)" IS NOT NULL AND d."fp time (sec)" < d1."fp time (sec)" THEN d."fp time (sec)"
--         /* WHEN d.[fp time (sec)] IS NOT NULL AND d1.[fp time (sec)] IS NOT NULL                                               THEN d.[fp time (sec)] - d1.[fp time (sec)] */
--             WHEN d."fp time (sec)" IS NULL AND d1."fp time (sec)" IS NOT NULL THEN NULL
--         END, d.seq
--         FROM tt AS d
--         LEFT OUTER JOIN tt AS d1
--             ON d.personid = d1.personid AND d.dateyear = d1.dateyear AND d.seq = d1.seq + 1)
--     UPDATE dbo.learnerdatareadingeggs AS d
--     SET "re week time (sec)" = u.removement, "rex week time (sec)" = u.rexmovement, "fp week time (sec)" = u.fpmovement
--     FROM dbo.learnerdatareadingeggs AS d
--     JOIN mm AS u
--         ON d_dml.personid = u.personid AND d_dml.date = u.date
--         WHERE date_part('year', d_dml.date) = par_CalcYear AND d.ORGANISATIONID = d_dml.ORGANISATIONID AND d.PERSONID = d_dml.PERSONID;
--     /* D.[Date] BETWEEN @FromDate AND @ToDate; */
--     GET DIAGNOSTICS var_rowcount = ROW_COUNT;
--     RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %', '50000', 0, 0, 'Rows set: %s', var_rowcount USING ERRCODE = '50000';
    /* SELECT * FROM mm */
    /* ORDER BY PersonID, DateYear, [Date] */
END;
$BODY$;
