namespace TMSDAL.Query.TrainingAttendance
{
    /// <summary>
    /// Required indexes:
    ///   IX_TTRAININGATTENDANCE_SESSID on (INSTANCESESSIONID)
    ///   IX_TTRAININGATTENDANCE_ENROLLMENTID on (ENROLLMENTID)
    /// </summary>
    public static class TrainingAttendanceQB
    {
        // ========================================================
        // GET SESSION ATTENDANCE
        // ========================================================
        public const string GET_SESSION_ATTENDANCE = @"
SELECT
    ta.TRAININGATTENDANCEID    AS AttendanceId,
    ta.INSTANCESESSIONID       AS InstanceSessionId,
    ta.ENROLLMENTID           AS EnrollmentId,
    e.EMPLOYEENAME            AS EmployeeName,
    ta.ATTENDANCESTATUS       AS AttendanceStatus,
    ta.ABSENCEREASON          AS AbsenceReason,
    ta.ATTENDANCEPCT          AS AttendancePct,
    ta.CHECKINTIME            AS CheckInTime,
    ta.CHECKOUTTIME           AS CheckOutTime,
    ta.RECORDEDBYID           AS RecordedById,
    ta.RECORDEDON             AS RecordedOn
FROM DBO.TTRAININGATTENDANCE ta
INNER JOIN DBO.TTRAININGENROLLMENT te
    ON te.ENROLLMENTID = ta.ENROLLMENTID
INNER JOIN DBO.MEMPLOYEE e
    ON e.EMPLOYEEID = te.EMPLOYEEID
WHERE ta.INSTANCESESSIONID = @InstanceSessionId
ORDER BY e.EMPLOYEENAME;";

        // ========================================================
        // GET ATTENDANCE LIST (ALL TENANTS)
        // ========================================================
        public const string GET_ATTENDANCE_LIST = @"
SELECT
    ta.TRAININGATTENDANCEID     AS AttendanceId,

    ta.INSTANCESESSIONID        AS InstanceSessionId,
    tis.SCHEDULEDDATE           AS ScheduledDate,

    ti.INSTANCEID               AS InstanceId,
    ti.INSTANCECODE             AS InstanceCode,

    tp.PROGRAMMEID              AS ProgrammeId,
    tp.PROGRAMMETITLE           AS ProgrammeTitle,

    ps.PROGRAMMESESSIONID       AS ProgrammeSessionId,
    ps.SESSIONTITLE             AS SessionTitle,

    te.ENROLLMENTID             AS EnrollmentId,
    emp.EMPLOYEEID              AS EmployeeId,
    emp.EMPLOYEENAME            AS EmployeeName,

    ta.ATTENDANCESTATUS         AS AttendanceStatus,
    ta.ATTENDANCEPCT            AS AttendancePct,
    ta.CHECKINTIME              AS CheckInTime,
    ta.CHECKOUTTIME             AS CheckOutTime,
    ta.ABSENCEREASON            AS AbsenceReason,

    ta.RECORDEDBYID             AS RecordedById,
    rec.EMPLOYEENAME            AS RecordedByName,
    ta.RECORDEDON               AS RecordedOn

FROM DBO.TTRAININGATTENDANCE ta
INNER JOIN DBO.TINSTANCESESSION tis
    ON tis.INSTANCESESSIONID = ta.INSTANCESESSIONID
INNER JOIN DBO.TTRAININGINSTANCE ti
    ON ti.INSTANCEID = tis.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME tp
    ON tp.PROGRAMMEID = ti.PROGRAMMEID
   AND tp.TENANTID    = ti.TENANTID
INNER JOIN DBO.MPROGRAMMESESSION ps
    ON ps.PROGRAMMESESSIONID = tis.PROGRAMMESESSIONID
INNER JOIN DBO.TTRAININGENROLLMENT te
    ON te.ENROLLMENTID = ta.ENROLLMENTID
INNER JOIN DBO.MEMPLOYEE emp
    ON emp.EMPLOYEEID = te.EMPLOYEEID
INNER JOIN DBO.MEMPLOYEE rec
    ON rec.EMPLOYEEID = ta.RECORDEDBYID
WHERE ti.TENANTID = @TenantId
ORDER BY ta.RECORDEDON DESC;";

        // ========================================================
        // BULK INSERT ATTENDANCE
        // ========================================================
        public const string BULK_INSERT_ATTENDANCE = @"INSERT INTO DBO.TTRAININGATTENDANCE
(
    TRAININGATTENDANCEID,
    INSTANCESESSIONID,
    ENROLLMENTID,
    ATTENDANCESTATUS,
    ATTENDANCEPCT,
    CHECKINTIME,
    CHECKOUTTIME,
    ABSENCEREASON,
    RECORDEDBYID,
    RECORDEDON
)
VALUES
(
    @AttendanceId,
    @InstanceSessionId,
    @EnrollmentId,
    @AttendanceStatus,
    @AttendancePct,
    @CheckInTime,
    @CheckOutTime,
    @AbsenceReason,
    @RecordedById,
    @RecordedOn
)";

        // ========================================================
        // DELETE SESSION ATTENDANCE
        // ========================================================
        public const string DELETE_SESSION_ATTENDANCE = @"
DELETE FROM DBO.TTRAININGATTENDANCE
WHERE INSTANCESESSIONID = @InstanceSessionId;";
    }
}