CREATE OR REPLACE PROCEDURE internal.ptransferquiz(IN par_dataimportid BIGINT, IN par_validateonly NUMERIC DEFAULT 0, 
    IN par_debugmode INTEGER DEFAULT 0, INOUT return_code int DEFAULT 0)
AS 
$BODY$
DECLARE
    var_TranCountOnEntry INTEGER
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the @@TRANCOUNT function. Create a user-defined function.]
    = @@TRANCOUNT
    */;
    var_SourceDataImportID BIGINT DEFAULT (SELECT
        sourcedataimportid
        FROM internal.dataimport
        WHERE dataimportid = par_DataImportID);
    var_SourceDataDate DATE;
    var_SourceDataDateS VARCHAR(20);
    var_dbname VARCHAR(128) DEFAULT current_database();
    var_LogDescription VARCHAR(255);
    var_LogData VARCHAR(2048);
    var_RowCount INTEGER;
    var_LogLevel CHAR(1);
    var_Sql TEXT;
    var_QuizID INTEGER;
    var_QuizInstanceID INTEGER;
    var_QuizInstanceSourceID INTEGER;
    presetsequence$ReturnCode INTEGER;
BEGIN
    /*
    2022-04-07	DP	Add use of LearnerDataQuizFixPersonMapping table.  This preserves the DataImportID/RowNumber mapping to PersonID because some of the
    				UserIDs have been lost due to registering/unregistering/registering outside of the normal process.
    2022-04-08	DP	Change the quiz selection:
    					Take the first submission except:
    						1.	Where multi-submission and complete on 2nd submission (but not 1st), take the 2nd submission.
    						2.	For all other submissions (multiple or single), take the first.
    */
    /*
    [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_DataImportID IS NULL THEN
        RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 16, 0, '@DataImportID must be supplied.' USING ERRCODE = '50000';
        return_code := 1;
        RETURN;
    END IF;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMAT(VARCHAR,VARCHAR) function. Create a user-defined function.]
    SELECT
    		@SourceDataDate		= DataContractDataDate,
    		@SourceDataDateS	= FORMAT(@SourceDataDate, 'yyyy-MM-dd')
    	FROM staging.DataImportDataContract
    	WHERE DataImportID = @SourceDataImportID
    */
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('SourceDataImportID=%I64d, @SourceDataDate=%s', @SourceDataImportID, @SourceDataDateS);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    CREATE TEMPORARY TABLE t$submission
    (id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
        /* */
        rownumber BIGINT NOT NULL,
        submissionid VARCHAR(50) NOT NULL,
        sourcequizid VARCHAR(50) NOT NULL,
        learnerid VARCHAR(50) NOT NULL,
        startdatetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        complete NUMERIC(1, 0) NOT NULL,
        /* */
        personid INTEGER,
        quizid INTEGER,
        quizinstanceid INTEGER,
        quizinstancesourceid INTEGER,
        learnerdataquizid BIGINT,
        personseq INTEGER,
        personsubmissioncount INTEGER,
        ignore NUMERIC(1, 0) DEFAULT (0));
    CREATE TEMPORARY TABLE t$response
    (rownumber BIGINT NOT NULL,
        submissionid VARCHAR(50) NOT NULL,
        questionexternalid VARCHAR(50) NOT NULL,
        value VARCHAR(250) NOT NULL,
        startdatetime TIMESTAMP WITHOUT TIME ZONE NULL,
        enddatetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
        /* */
        quizquestiongroupid INTEGER,
        quizquestionid INTEGER,
        result SMALLINT);
    INSERT INTO t$submission (rownumber, submissionid, sourcequizid, learnerid, startdatetime, complete)
    SELECT
        s.rownumber, s.submissionid, quizid, learnerid, r.enddatetime, complete
        FROM staging.quizsubmission AS s
        JOIN staging.quizsubmissionresponse AS r
            ON LOWER(s.submissionid) = LOWER(r.submissionid) AND (LOWER(r.questionexternalid) = LOWER('re_username') OR LOWER(r.questionexternalid) = LOWER('LearnerID'))
        WHERE s.dataimportid = var_SourceDataImportID
        ORDER BY learnerid NULLS FIRST, r.enddatetime NULLS FIRST;
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Submission Rows: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    CREATE INDEX ix_t$submission_ix_submission_learnerid_enddatetime ON t$submission
        (learnerid, startdatetime);
    CREATE UNIQUE INDEX ix_t$submission_ix_id ON t$submission
        (id);
    /* Set Quiz IDs */
    
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    UPDATE S SET
    		QuizID					= QI.QuizID,
    		QuizInstanceID			= QI.QuizInstanceID,
    		QuizInstanceSourceID	= QIS.QuizInstanceSourceID
    	FROM #Submission S
    	JOIN QuizInstanceSource QIS ON S.SourceQuizID = QIS.SourceQuizID AND @SourceDataDate BETWEEN QIS.StartDate AND QIS.EndDate
    	JOIN QuizInstanceHasQuizInstanceSource QI_QIS ON QIS.QuizInstanceSourceID = QI_QIS.QuizInstanceSourceID
    	JOIN QuizInstance QI ON QI_QIS.QuizInstanceID = QI.QuizInstanceID AND @SourceDataDate BETWEEN QI.StartDate AND QI.EndDate
    */
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Quiz Mapped: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    /* Delete unmatched quizzes */
    DELETE FROM t$submission
        WHERE quizid IS NULL;
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Deleting Submissions with no mapped quiz: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    SELECT
        quizinstancesourceid, quizinstanceid, quizid
        INTO var_QuizInstanceSourceID, var_QuizInstanceID, var_QuizID
        FROM t$submission
        LIMIT 1;
    RAISE 'Error %, severity %, state % was raised. Message: %. Argument: %. Argument: %. Argument: %', '50000', 0, 0, '@QuizID=%d, @QuizInstanceID=%d, @QuizInstanceSourceID=%d', var_QuizID, var_QuizInstanceID, var_QuizInstanceSourceID USING ERRCODE = '50000';
    /* Match by username for these quizes because they were prior to the use of UserID */
    /* Click Foundation eQuiz - Comprehension, Click Foundation eQuiz - Literacy */

    IF var_QuizInstanceSourceID IN (1, 2) THEN
        UPDATE t$submission AS s
        SET personid = pp.personid
        FROM dbo.personhasprogramme AS pp
        JOIN dbo.person AS p
            ON pp.personid = p_dml.personid
            WHERE s.personid IS NULL AND LOWER(s.learnerid) = LOWER(pp.username);
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Person Matched (by UserName): %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$submission AS s
        SET personid = x.personid
        FROM t$submission
        CROSS JOIN LATERAL (SELECT
            personid
            FROM dbo.personhasprogrammehistory
            WHERE LOWER(username) = LOWER(s.learnerid) AND programmeid = 4
            ORDER BY sysendtime DESC NULLS FIRST
            LIMIT 1) AS x
        JOIN dbo.person AS p
            ON x.personid = p_dml.personid
            WHERE s.personid IS NULL;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Person Matched (by UserName) (H): %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    END IF;
    /* Mactch by UserID */
    
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    UPDATE S SET PersonID = PP.PersonID
    	FROM #Submission S
    	JOIN PersonHasProgramme PP ON TRY_CAST(S.LearnerID AS uniqueidentifier) = PP.UserID
    	JOIN Person P ON PP.PersonID = P.PersonID
    	WHERE S.PersonID IS NULL
    */
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Person Matched (by UserID): %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the TRY_CAST function. Create a user-defined function.]
    UPDATE S SET PersonID = X.PersonID
    	FROM #Submission S
    	CROSS APPLY (SELECT TOP 1 PersonID FROM PersonHasProgrammeHistory WHERE TRY_CAST(S.LearnerID AS uniqueidentifier) = UserID ORDER BY SysEndTime DESC) X
    	JOIN Person P ON X.PersonID = P.PersonID
    	WHERE S.PersonID IS NULL
    */
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Person Matched (by UserID) (H): %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    /* If the fix mapping table exists, match anything left */

    IF aws_sqlserver_ext.object_id('internal.LearnerDataQuizFixPersonMapping') IS NOT NULL THEN
        /*
        [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
        UPDATE S SET PersonID = M.PersonID
        		FROM #Submission S
        		JOIN internal.LearnerDataQuizFixPersonMapping M ON M.SourceDataImportID = @SourceDataImportID AND S.RowNumber = M.SourceRowNumber
        		WHERE S.PersonID IS NULL
        */
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Person Matched (by fix table): %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    END IF;
    /* Delete any submissions from unknown persons */
    DELETE FROM t$submission
        WHERE personid IS NULL;
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Deleting Submissions with no person match: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    /* Look in prior submission within same quizinstance (there will be at most one) */
    
    /*
    [7773 - Severity CRITICAL - Unable to perform an automated migration of arithmetic operations with dates. Make cast operands to the expected type., 7773 - Severity CRITICAL - Unable to perform an automated migration of arithmetic operations with dates. Make cast operands to the expected type.]
    INSERT #Submission
    	([RowNumber], [SubmissionID], [SourceQuizID], [LearnerID], [StartDateTime], [Complete], PersonID, LearnerDataQuizID, QuizID, QuizInstanceID, QuizInstanceSourceID)
    	SELECT -LDQ.SourceRowNumber, '', '', '', CAST(LDQ.[Date] AS datetime)+CAST(LDQ.[Time] AS datetime), LDQ.Complete, LDQ.PersonID, LDQ.LearnerDataQuizID, @QuizID, @QuizInstanceID, @QuizInstanceSourceID
    	FROM #Submission SS
    	JOIN LearnerDataQuiz LDQ ON SS.PersonID = LDQ.PersonID AND SS.QuizInstanceID = LDQ.QuizInstanceID AND (CAST(LDQ.[Date] AS datetime)+CAST(LDQ.[Time] AS datetime)) <= SS.StartDateTime
    */
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Insert prior submission from prior dataset: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    /* Number the submissions */
    UPDATE t$submission AS ss
    SET personseq = u.seq
    FROM (SELECT
        u.id, ROW_NUMBER() OVER (PARTITION BY u.personid ORDER BY u.startdatetime) AS seq
        FROM t$submission AS u) AS u
        WHERE ss.id = u.id;
    UPDATE t$submission AS ss
    SET personsubmissioncount = u.personsubmissioncount
    FROM (SELECT
        u.personid, COUNT(*) AS personsubmissioncount
        FROM t$submission AS u
        GROUP BY u.personid) AS u
        WHERE ss.personid = u.personid;
    /* Ignore the first submission if first is incomplete and second is complete */
    
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    UPDATE SS1 SET Ignore = 1
    	FROM #Submission SS1
    	JOIN #Submission SS2 ON SS1.PersonID = SS2.PersonID AND SS2.PersonSeq = 2
    	WHERE SS1.PersonSubmissionCount > 1 AND SS1.PersonSeq = 1 AND SS1.Complete = 0 AND SS2.Complete = 1
    */
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Ignoring first because it is incomplete and second is complete: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    /*
    [9996 - Severity CRITICAL - Transformer error occurred. Please submit report to developers.]
    UPDATE SS SET Ignore = CASE
    			WHEN SS.PersonSeq = 2 AND SS1.Ignore = 1 THEN 0
    			ELSE 1
    		END
    	FROM #Submission SS
    	JOIN #Submission SS1 ON SS.PersonID = SS1.PersonID AND SS1.PersonSeq = 1
    	WHERE SS.PersonSeq >= 2
    */
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Ignoring all other multi submissions except first: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    INSERT INTO t$response (rownumber, submissionid, questionexternalid, value, startdatetime, enddatetime, quizquestionid, quizquestiongroupid)
    SELECT
        sr.rownumber, sr.submissionid, sr.questionexternalid, srm.value, sr.startdatetime, sr.enddatetime, qism.quizquestionid, qq.quizquestiongroupid
        FROM staging.quizsubmissionresponse AS sr
        JOIN t$submission AS ss
            ON LOWER(sr.submissionid) = LOWER(ss.submissionid) AND ss.ignore = 0 AND LOWER(ss.submissionid) <> LOWER('')
        JOIN
        /* ignore submissions from prior datasets and Ignored submissions */
        dbo.quizinstancesourcequestionmap AS qism
            ON ss.quizinstancesourceid = qism.quizinstancesourceid AND LOWER(sr.questionexternalid) = LOWER(qism.sourcequestionid)
        JOIN staging.quizsubmissionresponse AS srm
            ON LOWER(srm.submissionid) = LOWER(ss.submissionid) AND LOWER(sr.questionexternalid || '_Mark') = LOWER(srm.questionexternalid)
        JOIN dbo.quizhasquizquestion AS q_qq
            ON ss.quizid = q_qq.quizid AND qism.quizquestionid = q_qq.quizquestionid
        JOIN dbo.quizquestion AS qq
            ON q_qq.quizquestionid = qq.quizquestionid
        JOIN dbo.quizhasquizquestiongroup AS q_qqg
            ON ss.quizid = q_qqg.quizid AND qq.quizquestiongroupid = q_qqg.quizquestiongroupid
        WHERE sr.dataimportid = var_SourceDataImportID;
    GET DIAGNOSTICS var_RowCount = ROW_COUNT;
    /*
    [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
    SET @LogDescription = FORMATMESSAGE('Response Rows: %d', @RowCOunt);
    */
    CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
    /* SELECT * FROM #Response; */
    CREATE INDEX ix_t$response_ix_response_submissionid ON t$response
        (submissionid);
    UPDATE t$response
    SET result = CAST (value AS SMALLINT);
    CREATE TEMPORARY TABLE t$data
    AS
    WITH data1 (rownumber, quizstartdatetime, submissionid, personid, quizid, quizinstanceid, quizquestiongroupid, quizquestionid, sequence, result, questionenddatetime, complete)
    AS (SELECT
        ss.rownumber, ss.startdatetime, ss.submissionid, ss.personid, ss.quizid, ss.quizinstanceid, qq.quizquestiongroupid, qq.quizquestionid, q_qq.sequence AS quizquestionsequence, sr.result, sr.enddatetime AS questionenddatetime, ss.complete
        FROM t$submission AS ss
        JOIN dbo.quizhasquizquestion AS q_qq
            ON ss.quizid = q_qq.quizid
        JOIN dbo.quizquestion AS qq
            ON q_qq.quizquestionid = qq.quizquestionid
        JOIN dbo.quizhasquizquestiongroup AS q_qqg
            ON ss.quizid = q_qqg.quizid AND qq.quizquestiongroupid = q_qqg.quizquestiongroupid
        LEFT OUTER JOIN t$response AS sr
            ON ss.submissionid = sr.submissionid AND q_qq.quizquestionid = sr.quizquestionid
        WHERE ss.ignore = 0 AND ss.submissionid <> ''), data (rownumber, submissionid, date, time, quizstartdatetime, personid, quizid, quizinstanceid, sequence, quizquestiongroupid, quizquestionid, result, questionenddatetime, timetaken, complete)
    AS (SELECT
        d.rownumber, d.submissionid, CAST (d.quizstartdatetime AS DATE), CAST (d.quizstartdatetime AS TIME(6) WITHOUT TIME ZONE), d.quizstartdatetime, d.personid, d.quizid, d.quizinstanceid, d.sequence, d.quizquestiongroupid, d.quizquestionid, d.result, d.questionenddatetime,
        CASE
            WHEN aws_sqlserver_ext.datediff('hour', d.quizstartdatetime::TIMESTAMP, d.questionenddatetime::TIMESTAMP) <= 4 THEN
            CASE
                WHEN d.sequence = 1 AND d.quizstartdatetime < d.questionenddatetime THEN aws_sqlserver_ext.datediff('second', d.quizstartdatetime::TIMESTAMP, d.questionenddatetime::TIMESTAMP)
                WHEN d.sequence > 1 AND d1.questionenddatetime < d.questionenddatetime THEN aws_sqlserver_ext.datediff('second', d1.questionenddatetime::TIMESTAMP, d.questionenddatetime::TIMESTAMP)
                ELSE NULL
            END
            ELSE NULL
        END AS timetaken, d.complete
        FROM data1 AS d
        LEFT OUTER JOIN data1 AS d1
            ON d.submissionid = d1.submissionid AND d.sequence = d1.sequence + 1)
    SELECT
        ROW_NUMBER() OVER (ORDER BY quizstartdatetime) AS id, *, CAST (NULL AS BIGINT) AS learnerdataquizid, CAST (NULL AS BIGINT) AS learnerdataquizquestiongroupid
        FROM data;
    /* SELECT * FROM #Submission */
    CALL clickcommon_dbo.presetsequence(var_dbname, 'dbo', 'LearnerDataQuiz', 'LearnerDataQuizQuestionGroup', return_code => presetsequence$ReturnCode);

    BEGIN
        IF var_TranCountOnEntry = 0 THEN
            RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'BEGIN TRAN' USING ERRCODE = '50000';
            /*
            [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
            BEGIN TRAN
            */
        END IF;
        /* Prior submission to delete */
        CREATE TEMPORARY TABLE t$deletelearnerdataquizid
        AS
        SELECT DISTINCT
            ss.learnerdataquizid
            FROM t$submission AS ss
            WHERE ss.ignore = 1 AND ss.learnerdataquizid IS NOT NULL;
        /*
        [7833 - Severity CRITICAL - Migration @@rowcount function in current context is not supported. Perform a manual conversion.]
        SET @RowCount = @@ROWCOUNT;
        */
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Prior LearnerDataQuiz to delete: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /*
        [7773 - Severity CRITICAL - Unable to perform an automated migration of arithmetic operations with dates. Make cast operands to the expected type.]
        INSERT #DeleteLearnerDataQuizID
        		SELECT DISTINCT LDQ.LearnerDataQuizID
        		FROM #Submission SS
        		JOIN LearnerDataQuiz LDQ ON SS.PersonID = LDQ.PersonID AND SS.QuizInstanceID = LDQ.QuizInstanceID AND SS.StartDateTime = (CAST(LDQ.[Date] AS datetime)+CAST(LDQ.[Time] AS datetime))
        		LEFT JOIN #DeleteLearnerDataQuizID D ON LDQ.LearnerDataQuizID = D.LearnerDataQuizID
        		WHERE SS.Ignore = 0 AND SS.LearnerDataQuizID IS NULL AND D.LearnerDataQuizID IS NULL;
        */
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Matching LearnerDataQuiz to delete: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        INSERT INTO t$deletelearnerdataquizid
        SELECT DISTINCT
            ldq.learnerdataquizid
            FROM dbo.learnerdataquiz AS ldq
            LEFT OUTER JOIN t$deletelearnerdataquizid AS d
                ON ldq.learnerdataquizid = d.learnerdataquizid
            WHERE ldq.dataimportid = par_DataImportID AND d.learnerdataquizid IS NULL;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('Current DataImportID LearnerDataQuiz to delete: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        DELETE FROM dbo.learnerdataquizquestion AS LDQ
        USING t$deletelearnerdataquizid AS ldq, dbo.learnerdataquizquestion AS ldqq, dbo.learnerdataquizquestiongroup AS ldqqg
            WHERE ldqqg.learnerdataquizid = ldq.learnerdataquizid AND ldqq.learnerdataquizquestiongroupid = ldqqg.learnerdataquizquestiongroupid;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('LearnerDataQuizQuestion deleted: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        DELETE FROM dbo.learnerdataquizquestiongroup AS LDQQG
        USING t$deletelearnerdataquizid AS ldq
            WHERE ldqqg.learnerdataquizid = ldq.learnerdataquizid;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('LearnerDataQuizQuestionGroup deleted: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        DELETE FROM dbo.learnerdataquiz AS LDQ
        USING t$deletelearnerdataquizid AS x
            WHERE x.learnerdataquizid = ldq.learnerdataquizid;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('LearnerDataQuiz deleted: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        UPDATE t$data AS d
        SET learnerdataquizid = x.learnerdataquizid
        FROM (SELECT
            d.date, d.personid, d.quizid, d.quizinstanceid, nextval('LearnerDataQuizSequence') AS learnerdataquizid
            FROM t$data AS d
            GROUP BY d.date, d.personid, d.quizid, d.quizinstanceid) AS x
            WHERE d.date = x.date AND d.personid = x.personid AND d.quizid = x.quizid AND d.quizinstanceid = x.quizinstanceid;
        UPDATE t$data AS d
        SET learnerdataquizquestiongroupid = x.learnerdataquizquestiongroupid
        FROM (SELECT
            d.learnerdataquizid, d.quizquestiongroupid, nextval('LearnerDataQuizQuestionGroupSequence') AS learnerdataquizquestiongroupid
            FROM t$data AS d
            GROUP BY d.learnerdataquizid, d.quizquestiongroupid) AS x
            WHERE d.learnerdataquizid = x.learnerdataquizid AND d.quizquestiongroupid = x.quizquestiongroupid;
        /* SELECT * FROM #Data; */
        INSERT INTO dbo.learnerdataquiz (learnerdataquizid, date, personid, organisationid, dataimportid, sourcerownumber, quizid, timetaken, time, quizinstanceid, complete)
        SELECT
            d.learnerdataquizid, MIN(d.date), MIN(d.personid), 1 AS organisationid, par_DataImportID AS dataimportid, MIN(d.rownumber) AS rownumber, MIN(d.quizid), SUM(timetaken) AS timetaken, MIN(d.time) AS time, MIN(d.quizinstanceid), MIN(CAST (d.complete AS INTEGER))
            FROM t$data AS d
            GROUP BY d.learnerdataquizid;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('LearnerDataQuiz inserted: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        /* SELECT * FROM #Data D LEFT JOIN Person P ON D.PersonID = P.PersonID WHERE P.PersonID IS NULL */
        INSERT INTO dbo.learnerdataquizquestiongroup (learnerdataquizquestiongroupid, learnerdataquizid, quizquestiongroupid, goal, progress, captured, timetaken)
        SELECT
            d.learnerdataquizquestiongroupid, MIN(d.learnerdataquizid), MIN(d.quizquestiongroupid), COUNT(*) AS goal, COUNT(result) AS progress, MAX(d.questionenddatetime) AS captured, SUM(timetaken) AS timetaken
            FROM t$data AS d
            GROUP BY d.learnerdataquizquestiongroupid;
        /* ORDER BY D.[Date], D.PersonID, D.QuizID, D.QuizInstanceID, D.QuizQuestionGroupID */
        /* ORDER BY TimeTaken DESC */
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('LearnerDataQuizQuestionGroup inserted: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);
        INSERT INTO dbo.learnerdataquizquestion (learnerdataquizquestiongroupid, quizquestionid, result, captured, timetaken)
        SELECT
            d.learnerdataquizquestiongroupid, d.quizquestionid, d.result, d.questionenddatetime, d.timetaken
            FROM t$data AS d
            WHERE questionenddatetime IS NOT NULL;
        GET DIAGNOSTICS var_RowCount = ROW_COUNT;
        /*
        [7811 - Severity CRITICAL - PostgreSQL doesn't support the FORMATMESSAGE(NVARCHAR,<VARIABLE_ARGUMENTS>) function. Create a user-defined function.]
        SET @LogDescription = FORMATMESSAGE('LearnerDataQuizQuestion inserted: %d', @RowCOunt);
        */
        CALL internal.pdataimportloginsert(par_DataImportID, var_LogDescription);

        IF var_TranCountOnEntry = 0 THEN
            RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'COMMIT TRAN' USING ERRCODE = '50000';
            COMMIT;
        END IF;
        EXCEPTION
            WHEN OTHERS THEN
                IF var_TranCountOnEntry = 0 THEN
                    RAISE 'Error %, severity %, state % was raised. Message: %.', '50000', 0, 0, 'ROLLBACK TRAN (CATCH)' USING ERRCODE = '50000';
                    /*
                    [7807 - Severity CRITICAL - PostgreSQL does not support explicit transaction management in functions. Perform a manual conversion.]
                    ROLLBACK TRAN;
                    */
                END IF;
                RAISE;
    END;
    return_code := 0;
    RETURN;
    /*
    
    DROP TABLE IF EXISTS t$submission;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$response;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$data;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
    /*
    
    DROP TABLE IF EXISTS t$deletelearnerdataquizid;
    */
    /*
    
    Temporary table must be removed before end of the function.
    */
END;
$BODY$
LANGUAGE plpgsql;