namespace TMSDAL.Query.TrainingCompletion
{
    public static class TrainingCompletionQB
    {
        public const string GET_COMPLETION = @"
            SELECT  tc.TRAININGCOMPLETIONID    AS TrainingCompletionId,
                    tc.TRAININGCOMPLETIONCODE  AS TrainingCompletionCode,
                    tc.TRAININGCOMPLETIONNAME  AS TrainingCompletionName,
                    tc.ENROLLMENTID            AS EnrollmentId,
                    te.ENROLLMENTCODE          AS EnrollmentCode,
                    te.ENROLLMENTNAME          AS EnrollmentName,
                    tc.INSTANCEID              AS InstanceId,
                    ti.INSTANCECODE            AS InstanceCode,
                    ti.INSTANCETITLE           AS InstanceTitle,
                    tc.PROGRAMMEID             AS ProgrammeId,
                    tp.PROGRAMMECODE           AS ProgrammeCode,
                    tp.PROGRAMMETITLE          AS ProgrammeTitle,
                    tc.EMPLOYEEID              AS EmployeeId,
                    e.EMPLOYEECODE             AS EmployeeCode,
                    e.EMPLOYEENAME             AS EmployeeName,

                    tc.COMPLETIONSTATUS        AS CompletionStatus,
                    tc.OVERALLATTENDANCEPCT    AS OverallAttendancePct,
                    tc.PREASSESSMENTSCORE      AS PreAssessmentScore,
                    tc.POSTASSESSMENTSCORE     AS PostAssessmentScore,
                    tc.SCOREIMPROVEMENT        AS ScoreImprovement,

                    tc.ISPASSED                AS IsPassed,
                    tc.COMPLETEDON             AS CompletionDate,
                    tc.CERTIFICATEISSUED       AS CertificateIssued,
                    tc.EVALUATIONTRIGGERED     AS EvaluationTriggered,

                    tc.TRAINERREMARKS          AS TrainerRemarks,
                    tc.HRREMARKS               AS HRRemarks,

                    tc.VERSION                 AS Version,
                    tc.STATUS                  AS Status,
                    tc.SORTORDER               AS SortOrder,
                    tc.SOURCETYPE              AS SourceType,

                    tc.TENANTID                AS TenantId,
                    tc.CREATEDBYID             AS CreatedById,
                    tc.CREATEDON               AS CreatedOn,
                    tc.MODIFIEDBYID            AS ModifiedById,
                    tc.MODIFIEDON              AS ModifiedOn
            FROM DBO.TTRAININGCOMPLETION tc
            JOIN DBO.MTRAININGPROGRAMME tp
                ON tp.PROGRAMMEID = tc.PROGRAMMEID
               AND tp.TENANTID    = tc.TENANTID
            JOIN DBO.MEMPLOYEE e
                ON e.EMPLOYEEID = tc.EMPLOYEEID
            LEFT JOIN DBO.TTRAININGINSTANCE ti
                ON ti.INSTANCEID = tc.INSTANCEID
            LEFT JOIN DBO.TTRAININGENROLLMENT te
                ON te.ENROLLMENTID = tc.ENROLLMENTID
            WHERE tc.TRAININGCOMPLETIONID = @TRAININGCOMPLETIONID
              AND tc.TENANTID = @TenantId";

        public const string GET_COMPLETION_BY_ENROLLMENT = @"
           SELECT  tc.TRAININGCOMPLETIONID    AS TrainingCompletionId,
                    tc.TRAININGCOMPLETIONCODE  AS TrainingCompletionCode,
                    tc.TRAININGCOMPLETIONNAME  AS TrainingCompletionName,
                    tc.ENROLLMENTID            AS EnrollmentId,
                    te.ENROLLMENTCODE          AS EnrollmentCode,
                    te.ENROLLMENTNAME          AS EnrollmentName,
                    tc.INSTANCEID              AS InstanceId,
                    ti.INSTANCECODE            AS InstanceCode,
                    ti.INSTANCETITLE           AS InstanceTitle,
                    tc.PROGRAMMEID             AS ProgrammeId,
                    tp.PROGRAMMECODE           AS ProgrammeCode,
                    tp.PROGRAMMETITLE          AS ProgrammeTitle,
                    tc.EMPLOYEEID              AS EmployeeId,
                    e.EMPLOYEECODE             AS EmployeeCode,
                    e.EMPLOYEENAME             AS EmployeeName,

                    tc.COMPLETIONSTATUS        AS CompletionStatus,
                    tc.OVERALLATTENDANCEPCT    AS OverallAttendancePct,
                    tc.PREASSESSMENTSCORE      AS PreAssessmentScore,
                    tc.POSTASSESSMENTSCORE     AS PostAssessmentScore,
                    tc.SCOREIMPROVEMENT        AS ScoreImprovement,

                    tc.ISPASSED                AS IsPassed,
                    tc.COMPLETEDON             AS CompletionDate,
                    tc.CERTIFICATEISSUED       AS CertificateIssued,
                    tc.EVALUATIONTRIGGERED     AS EvaluationTriggered,

                    tc.TRAINERREMARKS          AS TrainerRemarks,
                    tc.HRREMARKS               AS HRRemarks,

                    tc.VERSION                 AS Version,
                    tc.STATUS                  AS Status,
                    tc.SORTORDER               AS SortOrder,
                    tc.SOURCETYPE              AS SourceType,

                    tc.TENANTID                AS TenantId,
                    tc.CREATEDBYID             AS CreatedById,
                    tc.CREATEDON               AS CreatedOn,
                    tc.MODIFIEDBYID            AS ModifiedById,
                    tc.MODIFIEDON              AS ModifiedOn
            FROM DBO.TTRAININGCOMPLETION tc
            JOIN DBO.MTRAININGPROGRAMME tp
                ON tp.PROGRAMMEID = tc.PROGRAMMEID
               AND tp.TENANTID    = tc.TENANTID
            JOIN DBO.MEMPLOYEE e
                ON e.EMPLOYEEID = tc.EMPLOYEEID
            LEFT JOIN DBO.TTRAININGINSTANCE ti
                ON ti.INSTANCEID = tc.INSTANCEID
            LEFT JOIN DBO.TTRAININGENROLLMENT te
                ON te.ENROLLMENTID = tc.ENROLLMENTID
            WHERE tc.ENROLLMENTID = @EnrollmentId
              AND tc.TENANTID = @TenantId";


        public const string SAVE_COMPLETION = @"
            INSERT INTO DBO.TTRAININGCOMPLETION
            (
                TRAININGCOMPLETIONID,
                TRAININGCOMPLETIONCODE,
                TRAININGCOMPLETIONNAME,
                ENROLLMENTID,
                INSTANCEID,
                PROGRAMMEID,
                EMPLOYEEID,

                COMPLETIONSTATUS,
                OVERALLATTENDANCEPCT,
                PREASSESSMENTSCORE,
                POSTASSESSMENTSCORE,
                SCOREIMPROVEMENT,

                ISPASSED,
                COMPLETEDON,
                CERTIFICATEISSUED,
                EVALUATIONTRIGGERED,

                TRAINERREMARKS,
                HRREMARKS,

                VERSION,
                STATUS,
                SORTORDER,
                SOURCETYPE,

                TENANTID,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @TRAININGCOMPLETIONID,
                @TrainingCompletionCode,
                @TrainingCompletionName,
                @EnrollmentId,
                @InstanceId,
                @ProgrammeId,
                @EmployeeId,

                @CompletionStatus,
                @OverallAttendancePct,
                @PreAssessmentScore,
                @PostAssessmentScore,
                @ScoreImprovement,

                @IsPassed,
                @CompletionDate,
                @CERTIFICATEISSUED,
                @EvaluationTriggered,

                @TrainerRemarks,
                @HrRemarks,

                @Version,
                @Status,
                @SortOrder,
                5,

                @TenantId,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn
            )";



        #region SELECT LIST

        public const string GET_SELECT_LIST = @"
SELECT  tc.TRAININGCOMPLETIONID    AS TrainingCompletionId,
        tc.TRAININGCOMPLETIONCODE  AS TrainingCompletionCode,
        tc.TRAININGCOMPLETIONNAME  AS TrainingCompletionName,
        tc.ENROLLMENTID            AS EnrollmentId,
        tc.INSTANCEID              AS InstanceId,
        ti.INSTANCECODE            AS InstanceCode,
        ti.INSTANCETITLE           AS InstanceTitle,
        tc.PROGRAMMEID             AS ProgrammeId,
        tp.PROGRAMMECODE           AS ProgrammeCode,
        tp.PROGRAMMETITLE          AS ProgrammeTitle,
        tc.EMPLOYEEID              AS EmployeeId,
        e.EMPLOYEECODE             AS EmployeeCode,
        e.EMPLOYEENAME             AS EmployeeName,
        tc.COMPLETIONSTATUS        AS CompletionStatus,
        tc.ISPASSED                AS IsPassed,
        tc.COMPLETEDON             AS CompletionDate,
        tc.CERTIFICATEISSUED       AS CertificateIssued
FROM DBO.TTRAININGCOMPLETION tc
JOIN DBO.MTRAININGPROGRAMME tp
    ON tp.PROGRAMMEID = tc.PROGRAMMEID
   AND tp.TENANTID    = tc.TENANTID
JOIN DBO.MEMPLOYEE e
    ON e.EMPLOYEEID = tc.EMPLOYEEID
LEFT JOIN DBO.TTRAININGINSTANCE ti
    ON ti.INSTANCEID = tc.INSTANCEID
WHERE tc.TENANTID = @TenantId
{DYNAMIC_WHERE}
ORDER BY EmployeeName";

        #endregion

        public const string UPDATE_COMPLETION = @"
            UPDATE DBO.TTRAININGCOMPLETION
            SET
                COMPLETIONSTATUS     = @CompletionStatus,
                OVERALLATTENDANCEPCT = @OverallAttendancePct,
                PREASSESSMENTSCORE   = @PreAssessmentScore,
                POSTASSESSMENTSCORE   = @PostAssessmentScore,
                SCOREIMPROVEMENT     = @ScoreImprovement,

                ISPASSED             = @IsPassed,
                COMPLETEDON          = @CompletionDate,
                CERTIFICATEISSUED  = @CERTIFICATEISSUED,
                EVALUATIONTRIGGERED  = @EvaluationTriggered,

                TRAINERREMARKS       = @TrainerRemarks,
                HRREMARKS            = @HrRemarks,

                MODIFIEDBYID         = @ModifiedById,
                MODIFIEDON           = @ModifiedOn
            WHERE TRAININGCOMPLETIONID = @TRAININGCOMPLETIONID
              AND TENANTID = @TenantId";

        public const string BULK_INSERT_COMPLETION = SAVE_COMPLETION;

        public const string BULK_UPDATE_COMPLETION = UPDATE_COMPLETION;

        public const string GET_TRANINING_COMPLETION_LIST = @"SELECT  tc.TRAININGCOMPLETIONID    AS TrainingCompletionId,
                    tc.TRAININGCOMPLETIONCODE  AS TrainingCompletionCode,
                    tc.TRAININGCOMPLETIONNAME  AS TrainingCompletionName,
                    tc.ENROLLMENTID            AS EnrollmentId,
                    te.ENROLLMENTCODE          AS EnrollmentCode,
                    te.ENROLLMENTNAME          AS EnrollmentName,
                    tc.INSTANCEID              AS InstanceId,
                    ti.INSTANCECODE            AS InstanceCode,
                    ti.INSTANCETITLE           AS InstanceTitle,
                    tc.PROGRAMMEID             AS ProgrammeId,
                    tp.PROGRAMMECODE           AS ProgrammeCode,
                    tp.PROGRAMMETITLE          AS ProgrammeTitle,
                    tc.EMPLOYEEID              AS EmployeeId,
                    e.EMPLOYEECODE             AS EmployeeCode,
                    e.EMPLOYEENAME             AS EmployeeName,

                    tc.COMPLETIONSTATUS        AS CompletionStatus,
                    tc.OVERALLATTENDANCEPCT    AS OverallAttendancePct,
                    tc.PREASSESSMENTSCORE      AS PreAssessmentScore,
                    tc.POSTASSESSMENTSCORE     AS PostAssessmentScore,
                    tc.SCOREIMPROVEMENT        AS ScoreImprovement,

                    tc.ISPASSED                AS IsPassed,
                    tc.COMPLETEDON             AS CompletionDate,
                    tc.CERTIFICATEISSUED       AS CertificateIssued,
                    tc.EVALUATIONTRIGGERED     AS EvaluationTriggered,

                    tc.TRAINERREMARKS          AS TrainerRemarks,
                    tc.HRREMARKS               AS HRRemarks,

                    tc.VERSION                 AS Version,
                    tc.STATUS                  AS Status,
                    tc.SORTORDER               AS SortOrder,
                    tc.SOURCETYPE              AS SourceType,

                    tc.TENANTID                AS TenantId,
                    tc.CREATEDBYID             AS CreatedById,
                    tc.CREATEDON               AS CreatedOn,
                    tc.MODIFIEDBYID            AS ModifiedById,
                    tc.MODIFIEDON              AS ModifiedOn
            FROM DBO.TTRAININGCOMPLETION tc
            JOIN DBO.MTRAININGPROGRAMME tp
                ON tp.PROGRAMMEID = tc.PROGRAMMEID
               AND tp.TENANTID    = tc.TENANTID
            JOIN DBO.MEMPLOYEE e
                ON e.EMPLOYEEID = tc.EMPLOYEEID
            LEFT JOIN DBO.TTRAININGINSTANCE ti
                ON ti.INSTANCEID = tc.INSTANCEID
            LEFT JOIN DBO.TTRAININGENROLLMENT te
                ON te.ENROLLMENTID = tc.ENROLLMENTID";
    }
}