package com.ustadmobile.core.db.dao

import androidx.paging.PagingSource
import com.ustadmobile.door.DoorDbType
import com.ustadmobile.door.EntityInsertionAdapter
import com.ustadmobile.door.PreparedStatementConfig
import com.ustadmobile.door.ext.prepareAndUseStatement
import com.ustadmobile.door.ext.prepareAndUseStatementAsync
import com.ustadmobile.door.flow.doorFlow
import com.ustadmobile.door.jdbc.PreparedStatement
import com.ustadmobile.door.jdbc.ext.executeQueryAsyncKmp
import com.ustadmobile.door.jdbc.ext.executeUpdateAsyncKmp
import com.ustadmobile.door.jdbc.ext.mapNextRow
import com.ustadmobile.door.jdbc.ext.mapRows
import com.ustadmobile.door.jdbc.ext.useResults
import com.ustadmobile.door.paging.DoorLimitOffsetPagingSource
import com.ustadmobile.door.room.RoomDatabase
import com.ustadmobile.lib.db.composites.ClazzEnrolmentAndPerson
import com.ustadmobile.lib.db.composites.ClazzEnrolmentAndPersonDetailDetails
import com.ustadmobile.lib.db.composites.CourseNameAndPersonName
import com.ustadmobile.lib.db.composites.PersonAndClazzMemberListDetails
import com.ustadmobile.lib.db.entities.Clazz
import com.ustadmobile.lib.db.entities.ClazzEnrolment
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithClazz
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithLeavingReason
import com.ustadmobile.lib.db.entities.ClazzEnrolmentWithPerson
import com.ustadmobile.lib.db.entities.CourseTerminology
import com.ustadmobile.lib.db.entities.LeavingReason
import com.ustadmobile.lib.db.entities.Person
import com.ustadmobile.lib.db.entities.PersonPicture
import com.ustadmobile.lib.db.entities.xapi.ActorEntity
import kotlin.Boolean
import kotlin.IllegalArgumentException
import kotlin.Int
import kotlin.Long
import kotlin.String
import kotlin.collections.List
import kotlinx.coroutines.flow.Flow

public class ClazzEnrolmentDao_JdbcImpl(
  public val _db: RoomDatabase,
) : ClazzEnrolmentDao() {
  public val _insertAdapterClazzEnrolment_abort: EntityInsertionAdapter<ClazzEnrolment> = object :
      EntityInsertionAdapter<ClazzEnrolment>(_db) {
    override fun makeSql(returnsId: Boolean): String = when(dbType) {
      DoorDbType.SQLITE -> {
        "INSERT INTO ClazzEnrolment (clazzEnrolmentUid, clazzEnrolmentPersonUid, clazzEnrolmentClazzUid, clazzEnrolmentDateJoined, clazzEnrolmentDateLeft, clazzEnrolmentRole, clazzEnrolmentAttendancePercentage, clazzEnrolmentActive, clazzEnrolmentLeavingReasonUid, clazzEnrolmentOutcome, clazzEnrolmentLocalChangeSeqNum, clazzEnrolmentMasterChangeSeqNum, clazzEnrolmentLastChangedBy, clazzEnrolmentLct) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      }
      DoorDbType.POSTGRES ->  {
        "INSERT INTO ClazzEnrolment (clazzEnrolmentUid, clazzEnrolmentPersonUid, clazzEnrolmentClazzUid, clazzEnrolmentDateJoined, clazzEnrolmentDateLeft, clazzEnrolmentRole, clazzEnrolmentAttendancePercentage, clazzEnrolmentActive, clazzEnrolmentLeavingReasonUid, clazzEnrolmentOutcome, clazzEnrolmentLocalChangeSeqNum, clazzEnrolmentMasterChangeSeqNum, clazzEnrolmentLastChangedBy, clazzEnrolmentLct) VALUES(COALESCE(?,nextval('ClazzEnrolment_clazzEnrolmentUid_seq')), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + if(returnsId) { " RETURNING clazzEnrolmentUid" } else "" 
      }
      else -> {
        throw IllegalArgumentException("Unsupported db type")
      }
    }

    override fun bindPreparedStmtToEntity(stmt: PreparedStatement, entity: ClazzEnrolment) {
      if(entity.clazzEnrolmentUid == 0L) {
        stmt.setObject(1, null)
      } else {
        stmt.setLong(1, entity.clazzEnrolmentUid)
      }
      stmt.setLong(2, entity.clazzEnrolmentPersonUid)
      stmt.setLong(3, entity.clazzEnrolmentClazzUid)
      stmt.setLong(4, entity.clazzEnrolmentDateJoined)
      stmt.setLong(5, entity.clazzEnrolmentDateLeft)
      stmt.setInt(6, entity.clazzEnrolmentRole)
      stmt.setFloat(7, entity.clazzEnrolmentAttendancePercentage)
      stmt.setBoolean(8, entity.clazzEnrolmentActive)
      stmt.setLong(9, entity.clazzEnrolmentLeavingReasonUid)
      stmt.setInt(10, entity.clazzEnrolmentOutcome)
      stmt.setLong(11, entity.clazzEnrolmentLocalChangeSeqNum)
      stmt.setLong(12, entity.clazzEnrolmentMasterChangeSeqNum)
      stmt.setInt(13, entity.clazzEnrolmentLastChangedBy)
      stmt.setLong(14, entity.clazzEnrolmentLct)
    }
  }

  override fun insertListAsync(entityList: List<ClazzEnrolment>) {
    _insertAdapterClazzEnrolment_abort.insertList(entityList)
  }

  public override fun insert(entity: ClazzEnrolment): Long {
    val _retVal = _insertAdapterClazzEnrolment_abort.insertAndReturnId(entity)
    return _retVal
  }

  public override suspend fun insertAsync(entity: ClazzEnrolment): Long {
    val _retVal = _insertAdapterClazzEnrolment_abort.insertAndReturnIdAsync(entity)
    return _retVal
  }

  public override fun insertList(entityList: List<ClazzEnrolment>) {
    _insertAdapterClazzEnrolment_abort.insertList(entityList)
  }

  override suspend fun updateAsync(entity: ClazzEnrolment): Int {
    var _result = 0
    val _sql =
        "UPDATE ClazzEnrolment SET clazzEnrolmentPersonUid = ?, clazzEnrolmentClazzUid = ?, clazzEnrolmentDateJoined = ?, clazzEnrolmentDateLeft = ?, clazzEnrolmentRole = ?, clazzEnrolmentAttendancePercentage = ?, clazzEnrolmentActive = ?, clazzEnrolmentLeavingReasonUid = ?, clazzEnrolmentOutcome = ?, clazzEnrolmentLocalChangeSeqNum = ?, clazzEnrolmentMasterChangeSeqNum = ?, clazzEnrolmentLastChangedBy = ?, clazzEnrolmentLct = ? WHERE clazzEnrolmentUid = ?"
    _db.prepareAndUseStatementAsync(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.clazzEnrolmentPersonUid)
      _stmt.setLong(2, entity.clazzEnrolmentClazzUid)
      _stmt.setLong(3, entity.clazzEnrolmentDateJoined)
      _stmt.setLong(4, entity.clazzEnrolmentDateLeft)
      _stmt.setInt(5, entity.clazzEnrolmentRole)
      _stmt.setFloat(6, entity.clazzEnrolmentAttendancePercentage)
      _stmt.setBoolean(7, entity.clazzEnrolmentActive)
      _stmt.setLong(8, entity.clazzEnrolmentLeavingReasonUid)
      _stmt.setInt(9, entity.clazzEnrolmentOutcome)
      _stmt.setLong(10, entity.clazzEnrolmentLocalChangeSeqNum)
      _stmt.setLong(11, entity.clazzEnrolmentMasterChangeSeqNum)
      _stmt.setInt(12, entity.clazzEnrolmentLastChangedBy)
      _stmt.setLong(13, entity.clazzEnrolmentLct)
      _stmt.setLong(14, entity.clazzEnrolmentUid)
      _result += _stmt.executeUpdateAsyncKmp()
    }
    return _result
  }

  public override fun update(entity: ClazzEnrolment) {
    val _sql =
        "UPDATE ClazzEnrolment SET clazzEnrolmentPersonUid = ?, clazzEnrolmentClazzUid = ?, clazzEnrolmentDateJoined = ?, clazzEnrolmentDateLeft = ?, clazzEnrolmentRole = ?, clazzEnrolmentAttendancePercentage = ?, clazzEnrolmentActive = ?, clazzEnrolmentLeavingReasonUid = ?, clazzEnrolmentOutcome = ?, clazzEnrolmentLocalChangeSeqNum = ?, clazzEnrolmentMasterChangeSeqNum = ?, clazzEnrolmentLastChangedBy = ?, clazzEnrolmentLct = ? WHERE clazzEnrolmentUid = ?"
    _db.prepareAndUseStatement(_sql) {
       _stmt ->
      _stmt.setLong(1, entity.clazzEnrolmentPersonUid)
      _stmt.setLong(2, entity.clazzEnrolmentClazzUid)
      _stmt.setLong(3, entity.clazzEnrolmentDateJoined)
      _stmt.setLong(4, entity.clazzEnrolmentDateLeft)
      _stmt.setInt(5, entity.clazzEnrolmentRole)
      _stmt.setFloat(6, entity.clazzEnrolmentAttendancePercentage)
      _stmt.setBoolean(7, entity.clazzEnrolmentActive)
      _stmt.setLong(8, entity.clazzEnrolmentLeavingReasonUid)
      _stmt.setInt(9, entity.clazzEnrolmentOutcome)
      _stmt.setLong(10, entity.clazzEnrolmentLocalChangeSeqNum)
      _stmt.setLong(11, entity.clazzEnrolmentMasterChangeSeqNum)
      _stmt.setInt(12, entity.clazzEnrolmentLastChangedBy)
      _stmt.setLong(13, entity.clazzEnrolmentLct)
      _stmt.setLong(14, entity.clazzEnrolmentUid)
      _stmt.executeUpdate()
    }
  }

  override fun findAllEnrolmentsByPersonAndClazzUid(personUid: Long, clazzUid: Long):
      Flow<List<ClazzEnrolmentWithLeavingReason>> = _db.doorFlow(arrayOf("LeavingReason", "Clazz",
      "ClazzEnrolment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT ClazzEnrolment.*, LeavingReason.*, 
      |               COALESCE(Clazz.clazzTimeZone, 'UTC') as timeZone
      |          FROM ClazzEnrolment 
      |               LEFT JOIN LeavingReason 
      |                         ON LeavingReason.leavingReasonUid = ClazzEnrolment.clazzEnrolmentLeavingReasonUid
      |               LEFT JOIN Clazz 
      |                         ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid
      |         WHERE clazzEnrolmentPersonUid = ? 
      |           AND ClazzEnrolment.clazzEnrolmentActive 
      |           AND clazzEnrolmentClazzUid = ? 
      |      ORDER BY clazzEnrolmentDateLeft DESC
      |           
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,personUid)
      _stmt.setLong(2,clazzUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_timeZone = _result.getString("timeZone")
          val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
          val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
          val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
          val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
          val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
          val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
          val _tmp_clazzEnrolmentAttendancePercentage =
              _result.getFloat("clazzEnrolmentAttendancePercentage")
          val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
          val _tmp_clazzEnrolmentLeavingReasonUid =
              _result.getLong("clazzEnrolmentLeavingReasonUid")
          val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
          val _tmp_clazzEnrolmentLocalChangeSeqNum =
              _result.getLong("clazzEnrolmentLocalChangeSeqNum")
          val _tmp_clazzEnrolmentMasterChangeSeqNum =
              _result.getLong("clazzEnrolmentMasterChangeSeqNum")
          val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
          val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
          var _tmp_LeavingReason_nullCount = 0
          val _tmp_leavingReasonUid = _result.getLong("leavingReasonUid")
          if(_result.wasNull()) _tmp_LeavingReason_nullCount++
          val _tmp_leavingReasonTitle = _result.getString("leavingReasonTitle")
          if(_result.wasNull()) _tmp_LeavingReason_nullCount++
          val _tmp_leavingReasonMCSN = _result.getLong("leavingReasonMCSN")
          if(_result.wasNull()) _tmp_LeavingReason_nullCount++
          val _tmp_leavingReasonCSN = _result.getLong("leavingReasonCSN")
          if(_result.wasNull()) _tmp_LeavingReason_nullCount++
          val _tmp_leavingReasonLCB = _result.getInt("leavingReasonLCB")
          if(_result.wasNull()) _tmp_LeavingReason_nullCount++
          val _tmp_leavingReasonLct = _result.getLong("leavingReasonLct")
          if(_result.wasNull()) _tmp_LeavingReason_nullCount++
          val _tmp_LeavingReason_isAllNull = _tmp_LeavingReason_nullCount == 6
          ClazzEnrolmentWithLeavingReason().apply {
            this.timeZone = _tmp_timeZone
            this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
            this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
            this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
            this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
            this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
            this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
            this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
            this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
            this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
            this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
            this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
            this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
            this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
            this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
            if(!_tmp_LeavingReason_isAllNull) {
              this.leavingReason = LeavingReason().apply {
                this.leavingReasonUid = _tmp_leavingReasonUid
                this.leavingReasonTitle = _tmp_leavingReasonTitle
                this.leavingReasonMCSN = _tmp_leavingReasonMCSN
                this.leavingReasonCSN = _tmp_leavingReasonCSN
                this.leavingReasonLCB = _tmp_leavingReasonLCB
                this.leavingReasonLct = _tmp_leavingReasonLct
              }
            }
          }
        }
      }
    }
  }

  override suspend fun findEnrolmentWithLeavingReason(enrolmentUid: Long):
      ClazzEnrolmentWithLeavingReason? = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |            SELECT ClazzEnrolment.*, 
    |                   LeavingReason.*,
    |                   COALESCE(Clazz.clazzTimeZone, 'UTC') AS timeZone
    |              FROM ClazzEnrolment 
    |                   LEFT JOIN LeavingReason 
    |                             ON LeavingReason.leavingReasonUid = ClazzEnrolment.clazzEnrolmentLeavingReasonUid
    |                   LEFT JOIN Clazz 
    |                             ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid
    |             WHERE ClazzEnrolment.clazzEnrolmentUid = ?
    |             
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,enrolmentUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_timeZone = _result.getString("timeZone")
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        var _tmp_LeavingReason_nullCount = 0
        val _tmp_leavingReasonUid = _result.getLong("leavingReasonUid")
        if(_result.wasNull()) _tmp_LeavingReason_nullCount++
        val _tmp_leavingReasonTitle = _result.getString("leavingReasonTitle")
        if(_result.wasNull()) _tmp_LeavingReason_nullCount++
        val _tmp_leavingReasonMCSN = _result.getLong("leavingReasonMCSN")
        if(_result.wasNull()) _tmp_LeavingReason_nullCount++
        val _tmp_leavingReasonCSN = _result.getLong("leavingReasonCSN")
        if(_result.wasNull()) _tmp_LeavingReason_nullCount++
        val _tmp_leavingReasonLCB = _result.getInt("leavingReasonLCB")
        if(_result.wasNull()) _tmp_LeavingReason_nullCount++
        val _tmp_leavingReasonLct = _result.getLong("leavingReasonLct")
        if(_result.wasNull()) _tmp_LeavingReason_nullCount++
        val _tmp_LeavingReason_isAllNull = _tmp_LeavingReason_nullCount == 6
        ClazzEnrolmentWithLeavingReason().apply {
          this.timeZone = _tmp_timeZone
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
          if(!_tmp_LeavingReason_isAllNull) {
            this.leavingReason = LeavingReason().apply {
              this.leavingReasonUid = _tmp_leavingReasonUid
              this.leavingReasonTitle = _tmp_leavingReasonTitle
              this.leavingReasonMCSN = _tmp_leavingReasonMCSN
              this.leavingReasonCSN = _tmp_leavingReasonCSN
              this.leavingReasonLCB = _tmp_leavingReasonLCB
              this.leavingReasonLct = _tmp_leavingReasonLct
            }
          }
        }
      }
    }
  }

  override suspend fun updateDateLeftByUid(
    clazzEnrolmentUid: Long,
    endDate: Long,
    updateTime: Long,
  ) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        UPDATE ClazzEnrolment 
      |          SET clazzEnrolmentDateLeft = ?,
      |              clazzEnrolmentLct = ?
      |        WHERE clazzEnrolmentUid = ?
      """.trimMargin(),
      readOnly = false,)
    ) { _stmt -> 
      _stmt.setLong(1,endDate)
      _stmt.setLong(2,updateTime)
      _stmt.setLong(3,clazzEnrolmentUid)
      _stmt.executeUpdateAsyncKmp()
    }
  }

  override fun findAllClazzesByPersonWithClazz(accountPersonUid: Long, otherPersonUid: Long):
      Flow<List<ClazzEnrolmentAndPersonDetailDetails>> = _db.doorFlow(arrayOf("SystemPermission",
      "CourseTerminology", "Clazz", "ClazzEnrolment", "CoursePermission")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |               /* List of all CoursePermissions that are granted to the person as per accountPersonUid */
      |          WITH CoursePermissionsForAccountPerson AS (
      |               
      |        /* Get CoursePermissions given to the active user based on their enrolment role*/
      |        SELECT CoursePermission.*
      |          FROM ClazzEnrolment ClazzEnrolment_ActiveUser
      |               JOIN CoursePermission 
      |                    ON CoursePermission.cpClazzUid = ClazzEnrolment_ActiveUser.clazzEnrolmentClazzUid
      |                   AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ActiveUser.clazzEnrolmentRole
      |         WHERE ClazzEnrolment_ActiveUser.clazzEnrolmentPersonUid = ? 
      |         UNION
      |        /* Get ClazzUids where the active user can view members based a grant directly to them */
      |        SELECT CoursePermission.*
      |          FROM CoursePermission
      |         WHERE CoursePermission.cpToPersonUid  = ?
      |    ),
      |               /* Check if CoursePermission for accountPersonUid grants view permission */
      |               CanViewPersonUidViaCoursePermission(personUid) AS (
      |                    SELECT ClazzEnrolment.clazzEnrolmentPersonUid
      |                      FROM CoursePermissionsForAccountPerson
      |                           JOIN ClazzEnrolment 
      |                                ON (CoursePermissionsForAccountPerson.cpPermissionsFlag & 8192) > 0
      |                               AND ClazzEnrolment.clazzEnrolmentClazzUid = CoursePermissionsForAccountPerson.cpClazzUid  
      |                     WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ?         
      |               )     
      |        SELECT ClazzEnrolment.*,
      |               Clazz.*,
      |               CourseTerminology.*
      |          FROM ClazzEnrolment
      |               JOIN Clazz 
      |                    ON Clazz.clazzUid = ClazzEnrolment.clazzEnrolmentClazzUid
      |               LEFT JOIN CourseTerminology
      |                    ON CourseTerminology.ctUid = Clazz.clazzTerminologyUid
      |         WHERE (? != 0 AND ? != 0)
      |           AND ClazzEnrolment.clazzEnrolmentPersonUid = ?
      |               /* Check that accountPersonUid has permission to see otherPerson */
      |           AND (    (SELECT ? = ?)
      |                 OR (SELECT 
      |        EXISTS(SELECT 1
      |                 FROM SystemPermission
      |                WHERE ? != 0 
      |                  AND SystemPermission.spToPersonUid = ?
      |                  AND (SystemPermission.spPermissionsFlag &
      |     
      |                            8192
      |                            
      |        ) > 0
      |                  AND NOT SystemPermission.spIsDeleted)
      |    )
      |                 OR (SELECT ? IN 
      |                             (SELECT CanViewPersonUidViaCoursePermission.personUid
      |                                 FROM CanViewPersonUidViaCoursePermission))           
      |               ) 
      |              /* Check that accountPersonUid has permission to see related Clazz */
      |          AND (     (SELECT ? = ?)
      |                 OR (SELECT 
      |        EXISTS(SELECT 1
      |                 FROM SystemPermission
      |                WHERE ? != 0 
      |                  AND SystemPermission.spToPersonUid = ?
      |                  AND (SystemPermission.spPermissionsFlag &
      |     
      |                            1
      |                            
      |        ) > 0
      |                  AND NOT SystemPermission.spIsDeleted)
      |    )
      |                 OR (EXISTS(SELECT 1
      |                              FROM CoursePermissionsForAccountPerson
      |                             WHERE CoursePermissionsForAccountPerson.cpClazzUid = ClazzEnrolment.clazzEnrolmentClazzUid
      |                               AND (CoursePermissionsForAccountPerson.cpPermissionsFlag & 1) > 0)) 
      |               )
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,accountPersonUid)
      _stmt.setLong(2,accountPersonUid)
      _stmt.setLong(3,otherPersonUid)
      _stmt.setLong(4,accountPersonUid)
      _stmt.setLong(5,otherPersonUid)
      _stmt.setLong(6,otherPersonUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,otherPersonUid)
      _stmt.setLong(9,accountPersonUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,otherPersonUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setLong(13,otherPersonUid)
      _stmt.setLong(14,accountPersonUid)
      _stmt.setLong(15,accountPersonUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          var _tmp_ClazzEnrolment_nullCount = 0
          val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentAttendancePercentage =
              _result.getFloat("clazzEnrolmentAttendancePercentage")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentLeavingReasonUid =
              _result.getLong("clazzEnrolmentLeavingReasonUid")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentLocalChangeSeqNum =
              _result.getLong("clazzEnrolmentLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentMasterChangeSeqNum =
              _result.getLong("clazzEnrolmentMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
          if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
          val _tmp_ClazzEnrolment_isAllNull = _tmp_ClazzEnrolment_nullCount == 14
          var _tmp_Clazz_nullCount = 0
          val _tmp_clazzUid = _result.getLong("clazzUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzName = _result.getString("clazzName")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzDesc = _result.getString("clazzDesc")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_attendanceAverage = _result.getFloat("attendanceAverage")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzHolidayUMCalendarUid = _result.getLong("clazzHolidayUMCalendarUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzScheuleUMCalendarUid = _result.getLong("clazzScheuleUMCalendarUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_isClazzActive = _result.getBoolean("isClazzActive")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzLocationUid = _result.getLong("clazzLocationUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzStartTime = _result.getLong("clazzStartTime")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzEndTime = _result.getLong("clazzEndTime")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzFeatures = _result.getLong("clazzFeatures")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzSchoolUid = _result.getLong("clazzSchoolUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzEnrolmentPolicy = _result.getInt("clazzEnrolmentPolicy")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzTerminologyUid = _result.getLong("clazzTerminologyUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzMasterChangeSeqNum = _result.getLong("clazzMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzLocalChangeSeqNum = _result.getLong("clazzLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzLastChangedBy = _result.getInt("clazzLastChangedBy")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzLct = _result.getLong("clazzLct")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzTimeZone = _result.getString("clazzTimeZone")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzStudentsPersonGroupUid = _result.getLong("clazzStudentsPersonGroupUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzTeachersPersonGroupUid = _result.getLong("clazzTeachersPersonGroupUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzPendingStudentsPersonGroupUid =
              _result.getLong("clazzPendingStudentsPersonGroupUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzParentsPersonGroupUid = _result.getLong("clazzParentsPersonGroupUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzCode = _result.getString("clazzCode")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_clazzOwnerPersonUid = _result.getLong("clazzOwnerPersonUid")
          if(_result.wasNull()) _tmp_Clazz_nullCount++
          val _tmp_Clazz_isAllNull = _tmp_Clazz_nullCount == 25
          var _tmp_CourseTerminology_nullCount = 0
          val _tmp_ctUid = _result.getLong("ctUid")
          if(_result.wasNull()) _tmp_CourseTerminology_nullCount++
          val _tmp_ctTitle = _result.getString("ctTitle")
          if(_result.wasNull()) _tmp_CourseTerminology_nullCount++
          val _tmp_ctTerminology = _result.getString("ctTerminology")
          if(_result.wasNull()) _tmp_CourseTerminology_nullCount++
          val _tmp_ctLct = _result.getLong("ctLct")
          if(_result.wasNull()) _tmp_CourseTerminology_nullCount++
          val _tmp_CourseTerminology_isAllNull = _tmp_CourseTerminology_nullCount == 4
          ClazzEnrolmentAndPersonDetailDetails().apply {
            if(!_tmp_ClazzEnrolment_isAllNull) {
              this.enrolment = ClazzEnrolment().apply {
                this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
                this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
                this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
                this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
                this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
                this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
                this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
                this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
                this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
                this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
                this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
                this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
                this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
                this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
              }
            }
            if(!_tmp_Clazz_isAllNull) {
              this.clazz = Clazz().apply {
                this.clazzUid = _tmp_clazzUid
                this.clazzName = _tmp_clazzName
                this.clazzDesc = _tmp_clazzDesc
                this.attendanceAverage = _tmp_attendanceAverage
                this.clazzHolidayUMCalendarUid = _tmp_clazzHolidayUMCalendarUid
                this.clazzScheuleUMCalendarUid = _tmp_clazzScheuleUMCalendarUid
                this.isClazzActive = _tmp_isClazzActive
                this.clazzLocationUid = _tmp_clazzLocationUid
                this.clazzStartTime = _tmp_clazzStartTime
                this.clazzEndTime = _tmp_clazzEndTime
                this.clazzFeatures = _tmp_clazzFeatures
                this.clazzSchoolUid = _tmp_clazzSchoolUid
                this.clazzEnrolmentPolicy = _tmp_clazzEnrolmentPolicy
                this.clazzTerminologyUid = _tmp_clazzTerminologyUid
                this.clazzMasterChangeSeqNum = _tmp_clazzMasterChangeSeqNum
                this.clazzLocalChangeSeqNum = _tmp_clazzLocalChangeSeqNum
                this.clazzLastChangedBy = _tmp_clazzLastChangedBy
                this.clazzLct = _tmp_clazzLct
                this.clazzTimeZone = _tmp_clazzTimeZone
                this.clazzStudentsPersonGroupUid = _tmp_clazzStudentsPersonGroupUid
                this.clazzTeachersPersonGroupUid = _tmp_clazzTeachersPersonGroupUid
                this.clazzPendingStudentsPersonGroupUid = _tmp_clazzPendingStudentsPersonGroupUid
                this.clazzParentsPersonGroupUid = _tmp_clazzParentsPersonGroupUid
                this.clazzCode = _tmp_clazzCode
                this.clazzOwnerPersonUid = _tmp_clazzOwnerPersonUid
              }
            }
            if(!_tmp_CourseTerminology_isAllNull) {
              this.courseTerminology = CourseTerminology().apply {
                this.ctUid = _tmp_ctUid
                this.ctTitle = _tmp_ctTitle
                this.ctTerminology = _tmp_ctTerminology
                this.ctLct = _tmp_ctLct
              }
            }
          }
        }
      }
    }
  }

  override fun findAllByPersonUid(personUid: Long): Flow<List<ClazzEnrolment>> =
      _db.doorFlow(arrayOf("ClazzEnrolment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |
      |        SELECT ClazzEnrolment.*
      |          FROM ClazzEnrolment
      |         WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ? 
      |    
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,personUid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
          val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
          val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
          val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
          val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
          val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
          val _tmp_clazzEnrolmentAttendancePercentage =
              _result.getFloat("clazzEnrolmentAttendancePercentage")
          val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
          val _tmp_clazzEnrolmentLeavingReasonUid =
              _result.getLong("clazzEnrolmentLeavingReasonUid")
          val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
          val _tmp_clazzEnrolmentLocalChangeSeqNum =
              _result.getLong("clazzEnrolmentLocalChangeSeqNum")
          val _tmp_clazzEnrolmentMasterChangeSeqNum =
              _result.getLong("clazzEnrolmentMasterChangeSeqNum")
          val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
          val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
          ClazzEnrolment().apply {
            this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
            this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
            this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
            this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
            this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
            this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
            this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
            this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
            this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
            this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
            this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
            this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
            this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
            this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
          }
        }
      }
    }
  }

  override suspend fun findAllClazzesByPersonWithClazzAsListAsync(personUid: Long):
      List<ClazzEnrolmentWithClazz> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |SELECT ClazzEnrolment.*, Clazz.* 
    |        FROM ClazzEnrolment 
    |        LEFT JOIN Clazz ON ClazzEnrolment.clazzEnrolmentClazzUid = Clazz.clazzUid 
    |        WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ? 
    |        AND ClazzEnrolment.clazzEnrolmentActive
    |        ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,personUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        var _tmp_Clazz_nullCount = 0
        val _tmp_clazzUid = _result.getLong("clazzUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzName = _result.getString("clazzName")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzDesc = _result.getString("clazzDesc")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_attendanceAverage = _result.getFloat("attendanceAverage")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzHolidayUMCalendarUid = _result.getLong("clazzHolidayUMCalendarUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzScheuleUMCalendarUid = _result.getLong("clazzScheuleUMCalendarUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_isClazzActive = _result.getBoolean("isClazzActive")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzLocationUid = _result.getLong("clazzLocationUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzStartTime = _result.getLong("clazzStartTime")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzEndTime = _result.getLong("clazzEndTime")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzFeatures = _result.getLong("clazzFeatures")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzSchoolUid = _result.getLong("clazzSchoolUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzEnrolmentPolicy = _result.getInt("clazzEnrolmentPolicy")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzTerminologyUid = _result.getLong("clazzTerminologyUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzMasterChangeSeqNum = _result.getLong("clazzMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzLocalChangeSeqNum = _result.getLong("clazzLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzLastChangedBy = _result.getInt("clazzLastChangedBy")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzLct = _result.getLong("clazzLct")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzTimeZone = _result.getString("clazzTimeZone")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzStudentsPersonGroupUid = _result.getLong("clazzStudentsPersonGroupUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzTeachersPersonGroupUid = _result.getLong("clazzTeachersPersonGroupUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzPendingStudentsPersonGroupUid =
            _result.getLong("clazzPendingStudentsPersonGroupUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzParentsPersonGroupUid = _result.getLong("clazzParentsPersonGroupUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzCode = _result.getString("clazzCode")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_clazzOwnerPersonUid = _result.getLong("clazzOwnerPersonUid")
        if(_result.wasNull()) _tmp_Clazz_nullCount++
        val _tmp_Clazz_isAllNull = _tmp_Clazz_nullCount == 25
        ClazzEnrolmentWithClazz().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
          if(!_tmp_Clazz_isAllNull) {
            this.clazz = Clazz().apply {
              this.clazzUid = _tmp_clazzUid
              this.clazzName = _tmp_clazzName
              this.clazzDesc = _tmp_clazzDesc
              this.attendanceAverage = _tmp_attendanceAverage
              this.clazzHolidayUMCalendarUid = _tmp_clazzHolidayUMCalendarUid
              this.clazzScheuleUMCalendarUid = _tmp_clazzScheuleUMCalendarUid
              this.isClazzActive = _tmp_isClazzActive
              this.clazzLocationUid = _tmp_clazzLocationUid
              this.clazzStartTime = _tmp_clazzStartTime
              this.clazzEndTime = _tmp_clazzEndTime
              this.clazzFeatures = _tmp_clazzFeatures
              this.clazzSchoolUid = _tmp_clazzSchoolUid
              this.clazzEnrolmentPolicy = _tmp_clazzEnrolmentPolicy
              this.clazzTerminologyUid = _tmp_clazzTerminologyUid
              this.clazzMasterChangeSeqNum = _tmp_clazzMasterChangeSeqNum
              this.clazzLocalChangeSeqNum = _tmp_clazzLocalChangeSeqNum
              this.clazzLastChangedBy = _tmp_clazzLastChangedBy
              this.clazzLct = _tmp_clazzLct
              this.clazzTimeZone = _tmp_clazzTimeZone
              this.clazzStudentsPersonGroupUid = _tmp_clazzStudentsPersonGroupUid
              this.clazzTeachersPersonGroupUid = _tmp_clazzTeachersPersonGroupUid
              this.clazzPendingStudentsPersonGroupUid = _tmp_clazzPendingStudentsPersonGroupUid
              this.clazzParentsPersonGroupUid = _tmp_clazzParentsPersonGroupUid
              this.clazzCode = _tmp_clazzCode
              this.clazzOwnerPersonUid = _tmp_clazzOwnerPersonUid
            }
          }
        }
      }
    }
  }

  override suspend fun getAllClazzEnrolledAtTimeAsync(
    clazzUid: Long,
    date: Long,
    roleFilter: Int,
    personUidFilter: Long,
  ): List<ClazzEnrolmentWithPerson> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ClazzEnrolment.*, Person.*
    |          FROM ClazzEnrolment
    |                LEFT JOIN Person 
    |                          ON ClazzEnrolment.clazzEnrolmentPersonUid = Person.personUid
    |        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |              AND ? BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
    |              AND ClazzEnrolment.clazzEnrolmentDateLeft
    |              AND CAST(clazzEnrolmentActive AS INTEGER) = 1
    |              AND (? = 0 OR ClazzEnrolment.clazzEnrolmentRole = ?)
    |              AND (? = 0 OR ClazzEnrolment.clazzEnrolmentPersonUid = ?)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,date)
    _stmt.setInt(3,roleFilter)
    _stmt.setInt(4,roleFilter)
    _stmt.setLong(5,personUidFilter)
    _stmt.setLong(6,personUidFilter)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        var _tmp_Person_nullCount = 0
        val _tmp_personUid = _result.getLong("personUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_username = _result.getString("username")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_firstNames = _result.getString("firstNames")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_lastName = _result.getString("lastName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_emailAddr = _result.getString("emailAddr")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_phoneNum = _result.getString("phoneNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_gender = _result.getInt("gender")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_active = _result.getBoolean("active")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personAddress = _result.getString("personAddress")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personOrgId = _result.getString("personOrgId")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLct = _result.getLong("personLct")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personCountry = _result.getString("personCountry")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personType = _result.getInt("personType")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_admin = _result.getBoolean("admin")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personNotes = _result.getString("personNotes")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherName = _result.getString("fatherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherName = _result.getString("motherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherNum = _result.getString("motherNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_Person_isAllNull = _tmp_Person_nullCount == 24
        ClazzEnrolmentWithPerson().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
          if(!_tmp_Person_isAllNull) {
            this.person = Person().apply {
              this.personUid = _tmp_personUid
              this.username = _tmp_username
              this.firstNames = _tmp_firstNames
              this.lastName = _tmp_lastName
              this.emailAddr = _tmp_emailAddr
              this.phoneNum = _tmp_phoneNum
              this.gender = _tmp_gender
              this.active = _tmp_active
              this.dateOfBirth = _tmp_dateOfBirth
              this.personAddress = _tmp_personAddress
              this.personOrgId = _tmp_personOrgId
              this.personGroupUid = _tmp_personGroupUid
              this.personLct = _tmp_personLct
              this.personCountry = _tmp_personCountry
              this.personType = _tmp_personType
              this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
              this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
              this.personLastChangedBy = _tmp_personLastChangedBy
              this.admin = _tmp_admin
              this.personNotes = _tmp_personNotes
              this.fatherName = _tmp_fatherName
              this.fatherNumber = _tmp_fatherNumber
              this.motherName = _tmp_motherName
              this.motherNum = _tmp_motherNum
            }
          }
        }
      }
    }
  }

  override suspend fun getAllEnrolmentsAtTimeByClazzAndPerson(
    clazzUid: Long,
    accountPersonUid: Long,
    time: Long,
  ): List<ClazzEnrolment> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ClazzEnrolment.*
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |           AND ClazzEnrolment.clazzEnrolmentPersonUid = ?
    |           AND ? BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
    |                         AND ClazzEnrolment.clazzEnrolmentDateLeft
    |           AND ClazzEnrolment.clazzEnrolmentActive              
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,time)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
        }
      }
    }
  }

  override suspend fun findByUid(uid: Long): ClazzEnrolment? =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = "SELECT * FROM ClazzEnrolment WHERE clazzEnrolmentUid = ?",
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,uid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
        }
      }
    }
  }

  override fun findByUidLive(uid: Long): Flow<ClazzEnrolment?> =
      _db.doorFlow(arrayOf("ClazzEnrolment")) {
    _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = "SELECT * FROM ClazzEnrolment WHERE clazzEnrolmentUid = ?",
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,uid)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(null) {
          val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
          val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
          val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
          val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
          val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
          val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
          val _tmp_clazzEnrolmentAttendancePercentage =
              _result.getFloat("clazzEnrolmentAttendancePercentage")
          val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
          val _tmp_clazzEnrolmentLeavingReasonUid =
              _result.getLong("clazzEnrolmentLeavingReasonUid")
          val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
          val _tmp_clazzEnrolmentLocalChangeSeqNum =
              _result.getLong("clazzEnrolmentLocalChangeSeqNum")
          val _tmp_clazzEnrolmentMasterChangeSeqNum =
              _result.getLong("clazzEnrolmentMasterChangeSeqNum")
          val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
          val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
          ClazzEnrolment().apply {
            this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
            this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
            this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
            this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
            this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
            this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
            this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
            this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
            this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
            this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
            this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
            this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
            this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
            this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
          }
        }
      }
    }
  }

  override fun findByClazzUidAndRole(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
    permission: Long,
  ): PagingSource<Int, PersonAndClazzMemberListDetails> = object :
      DoorLimitOffsetPagingSource<PersonAndClazzMemberListDetails>(db = _db
  , tableNames = arrayOf("Clazz", "Person", "ClazzEnrolment", "PersonPicture", "CoursePermission")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<PersonAndClazzMemberListDetails>
        = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT * 
      |          FROM (SELECT Person.*, PersonPicture.*,
      |                       (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, 
      |        
      |                       (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, 
      |        
      |                       (SELECT ClazzEnrolment.clazzEnrolmentRole 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid 
      |                           AND ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive
      |                      ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC
      |                         LIMIT 1) AS enrolmentRole
      |                  FROM Person
      |                       LEFT JOIN PersonPicture
      |                                 ON PersonPicture.personPictureUid = Person.personUid
      |                 WHERE Person.personUid IN 
      |                       (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid 
      |                          FROM ClazzEnrolment 
      |                         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive 
      |                           AND ClazzEnrolment.clazzEnrolmentRole = ? 
      |                           AND (? != 1 
      |                                 OR (? 
      |                                      BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
      |                                      AND ClazzEnrolment.clazzEnrolmentDateLeft))) 
      |                   /* Begin permission check */
      |                   AND (
      |                           (
      |         (
      |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
      |             (COALESCE(
      |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
      |                             FROM Clazz _Clazz_Permission
      |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
      |              /* 
      |              If there is a CoursePermission entity that is for the course as per the clazzUid
      |              parameter that is granted to the person directly or to the enrolmentRole that the 
      |              person has in the course, then permission is granted.
      |              */              
      |              OR EXISTS(SELECT CoursePermission.cpUid
      |                          FROM CoursePermission
      |                               
      |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
      |                        ON CoursePermission.cpToEnrolmentRole != 0
      |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
      |                           (SELECT COALESCE(
      |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
      |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
      |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
      |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
      |                                     LIMIT 1), 0))
      |    
      |                         WHERE CoursePermission.cpClazzUid = ?
      |                           AND (CoursePermission.cpToPersonUid = ? 
      |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
      |                           AND (CoursePermission.cpPermissionsFlag & 
      |         ?
      |         
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |         
      |        ) > 0)
      |               )
      |    
      |    )
      |                        OR Person.personUid = ?
      |                       )  
      |                   /* End permission check */                   
      |                   AND Person.firstNames || ' ' || Person.lastName LIKE ?
      |               GROUP BY Person.personUid, PersonPicture.personPictureUid) AS CourseMember
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN CourseMember.firstNames
      |                WHEN 3 THEN CourseMember.lastName
      |                ELSE ''
      |            END ASC,
      |            CASE(?)
      |                WHEN 2 THEN CourseMember.firstNames
      |                WHEN 4 THEN CourseMember.lastName
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN CourseMember.earliestJoinDate
      |                WHEN 9 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN CourseMember.earliestJoinDate
      |                WHEN 10 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END DESC
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,clazzUid)
      _stmt.setInt(3,roleId)
      _stmt.setInt(4,filter)
      _stmt.setLong(5,currentTime)
      _stmt.setLong(6,clazzUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,accountPersonUid)
      _stmt.setLong(9,clazzUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,permission)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setLong(13,permission)
      _stmt.setLong(14,accountPersonUid)
      _stmt.setString(15,searchText)
      _stmt.setInt(16,sortOrder)
      _stmt.setInt(17,sortOrder)
      _stmt.setInt(18,sortOrder)
      _stmt.setInt(19,sortOrder)
      _stmt.setInt(20,_limit)
      _stmt.setInt(21,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_earliestJoinDate = _result.getLong("earliestJoinDate")
          val _tmp_latestDateLeft = _result.getLong("latestDateLeft")
          val _tmp_enrolmentRole = _result.getInt("enrolmentRole")
          var _tmp_Person_nullCount = 0
          val _tmp_personUid = _result.getLong("personUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_username = _result.getString("username")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_firstNames = _result.getString("firstNames")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_lastName = _result.getString("lastName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_emailAddr = _result.getString("emailAddr")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_phoneNum = _result.getString("phoneNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_gender = _result.getInt("gender")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_active = _result.getBoolean("active")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personAddress = _result.getString("personAddress")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personOrgId = _result.getString("personOrgId")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personGroupUid = _result.getLong("personGroupUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLct = _result.getLong("personLct")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personCountry = _result.getString("personCountry")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personType = _result.getInt("personType")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_admin = _result.getBoolean("admin")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personNotes = _result.getString("personNotes")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherName = _result.getString("fatherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherNumber = _result.getString("fatherNumber")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherName = _result.getString("motherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherNum = _result.getString("motherNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_Person_isAllNull = _tmp_Person_nullCount == 24
          var _tmp_PersonPicture_nullCount = 0
          val _tmp_personPictureUid = _result.getLong("personPictureUid")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureLct = _result.getLong("personPictureLct")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureUri = _result.getString("personPictureUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_fileSize = _result.getInt("fileSize")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
          PersonAndClazzMemberListDetails().apply {
            this.earliestJoinDate = _tmp_earliestJoinDate
            this.latestDateLeft = _tmp_latestDateLeft
            this.enrolmentRole = _tmp_enrolmentRole
            if(!_tmp_Person_isAllNull) {
              this.person = Person().apply {
                this.personUid = _tmp_personUid
                this.username = _tmp_username
                this.firstNames = _tmp_firstNames
                this.lastName = _tmp_lastName
                this.emailAddr = _tmp_emailAddr
                this.phoneNum = _tmp_phoneNum
                this.gender = _tmp_gender
                this.active = _tmp_active
                this.dateOfBirth = _tmp_dateOfBirth
                this.personAddress = _tmp_personAddress
                this.personOrgId = _tmp_personOrgId
                this.personGroupUid = _tmp_personGroupUid
                this.personLct = _tmp_personLct
                this.personCountry = _tmp_personCountry
                this.personType = _tmp_personType
                this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
                this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
                this.personLastChangedBy = _tmp_personLastChangedBy
                this.admin = _tmp_admin
                this.personNotes = _tmp_personNotes
                this.fatherName = _tmp_fatherName
                this.fatherNumber = _tmp_fatherNumber
                this.motherName = _tmp_motherName
                this.motherNum = _tmp_motherNum
              }
            }
            if(!_tmp_PersonPicture_isAllNull) {
              this.personPicture = PersonPicture().apply {
                this.personPictureUid = _tmp_personPictureUid
                this.personPictureLct = _tmp_personPictureLct
                this.personPictureUri = _tmp_personPictureUri
                this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
                this.fileSize = _tmp_fileSize
                this.personPictureActive = _tmp_personPictureActive
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT * 
      |          FROM (SELECT Person.*, PersonPicture.*,
      |                       (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, 
      |        
      |                       (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, 
      |        
      |                       (SELECT ClazzEnrolment.clazzEnrolmentRole 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid 
      |                           AND ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive
      |                      ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC
      |                         LIMIT 1) AS enrolmentRole
      |                  FROM Person
      |                       LEFT JOIN PersonPicture
      |                                 ON PersonPicture.personPictureUid = Person.personUid
      |                 WHERE Person.personUid IN 
      |                       (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid 
      |                          FROM ClazzEnrolment 
      |                         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive 
      |                           AND ClazzEnrolment.clazzEnrolmentRole = ? 
      |                           AND (? != 1 
      |                                 OR (? 
      |                                      BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
      |                                      AND ClazzEnrolment.clazzEnrolmentDateLeft))) 
      |                   /* Begin permission check */
      |                   AND (
      |                           (
      |         (
      |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
      |             (COALESCE(
      |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
      |                             FROM Clazz _Clazz_Permission
      |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
      |              /* 
      |              If there is a CoursePermission entity that is for the course as per the clazzUid
      |              parameter that is granted to the person directly or to the enrolmentRole that the 
      |              person has in the course, then permission is granted.
      |              */              
      |              OR EXISTS(SELECT CoursePermission.cpUid
      |                          FROM CoursePermission
      |                               
      |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
      |                        ON CoursePermission.cpToEnrolmentRole != 0
      |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
      |                           (SELECT COALESCE(
      |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
      |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
      |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
      |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
      |                                     LIMIT 1), 0))
      |    
      |                         WHERE CoursePermission.cpClazzUid = ?
      |                           AND (CoursePermission.cpToPersonUid = ? 
      |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
      |                           AND (CoursePermission.cpPermissionsFlag & 
      |         ?
      |         
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     ?
      |         
      |        ) > 0)
      |               )
      |    
      |    )
      |                        OR Person.personUid = ?
      |                       )  
      |                   /* End permission check */                   
      |                   AND Person.firstNames || ' ' || Person.lastName LIKE ?
      |               GROUP BY Person.personUid, PersonPicture.personPictureUid) AS CourseMember
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN CourseMember.firstNames
      |                WHEN 3 THEN CourseMember.lastName
      |                ELSE ''
      |            END ASC,
      |            CASE(?)
      |                WHEN 2 THEN CourseMember.firstNames
      |                WHEN 4 THEN CourseMember.lastName
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN CourseMember.earliestJoinDate
      |                WHEN 9 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN CourseMember.earliestJoinDate
      |                WHEN 10 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END DESC
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,clazzUid)
      _stmt.setInt(3,roleId)
      _stmt.setInt(4,filter)
      _stmt.setLong(5,currentTime)
      _stmt.setLong(6,clazzUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,accountPersonUid)
      _stmt.setLong(9,clazzUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,permission)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setLong(13,permission)
      _stmt.setLong(14,accountPersonUid)
      _stmt.setString(15,searchText)
      _stmt.setInt(16,sortOrder)
      _stmt.setInt(17,sortOrder)
      _stmt.setInt(18,sortOrder)
      _stmt.setInt(19,sortOrder)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override fun findByClazzUidAndRoleForGradebook(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
  ): PagingSource<Int, PersonAndClazzMemberListDetails> = object :
      DoorLimitOffsetPagingSource<PersonAndClazzMemberListDetails>(db = _db
  , tableNames = arrayOf("Clazz", "Person", "ClazzEnrolment", "PersonPicture", "CoursePermission",
      "StatementEntity", "ActorEntity")
  ) {
    override suspend fun loadRows(_limit: Int, _offset: Int): List<PersonAndClazzMemberListDetails>
        = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT * FROM (
      |        SELECT * 
      |          FROM (SELECT Person.*, PersonPicture.*,
      |                       (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, 
      |        
      |                       (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, 
      |        
      |                       (SELECT ClazzEnrolment.clazzEnrolmentRole 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid 
      |                           AND ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive
      |                      ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC
      |                         LIMIT 1) AS enrolmentRole
      |                  FROM Person
      |                       LEFT JOIN PersonPicture
      |                                 ON PersonPicture.personPictureUid = Person.personUid
      |                       --Dummy join so that invalidations of the StatementEntity table will trigger
      |                       -- PagingSource invalidation of ClazzGradebookPagingSource
      |                       LEFT JOIN StatementEntity
      |                                 ON StatementEntity.statementIdHi = 0 
      |                                    AND StatementEntity.statementIdLo = 0
      |                       LEFT JOIN ActorEntity
      |                                 ON ActorEntity.actorUid = 0
      |                 WHERE Person.personUid IN 
      |                       (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid 
      |                          FROM ClazzEnrolment 
      |                         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive 
      |                           AND ClazzEnrolment.clazzEnrolmentRole = ? 
      |                           AND (? != 1 
      |                                 OR (? 
      |                                      BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
      |                                      AND ClazzEnrolment.clazzEnrolmentDateLeft))) 
      |                   /* Begin permission check */
      |                   AND (
      |                           ((
      |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
      |             (COALESCE(
      |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
      |                             FROM Clazz _Clazz_Permission
      |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
      |              /* 
      |              If there is a CoursePermission entity that is for the course as per the clazzUid
      |              parameter that is granted to the person directly or to the enrolmentRole that the 
      |              person has in the course, then permission is granted.
      |              */              
      |              OR EXISTS(SELECT CoursePermission.cpUid
      |                          FROM CoursePermission
      |                               
      |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
      |                        ON CoursePermission.cpToEnrolmentRole != 0
      |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
      |                           (SELECT COALESCE(
      |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
      |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
      |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
      |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
      |                                     LIMIT 1), 0))
      |    
      |                         WHERE CoursePermission.cpClazzUid = ?
      |                           AND (CoursePermission.cpToPersonUid = ? 
      |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
      |                           AND (CoursePermission.cpPermissionsFlag & 
      |         128
      |                            
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     128
      |                            
      |        ) > 0)
      |               )
      |    )
      |                        OR Person.personUid = ?
      |                       )  
      |                   /* End permission check */                   
      |                   AND Person.firstNames || ' ' || Person.lastName LIKE ?
      |               GROUP BY Person.personUid, PersonPicture.personPictureUid) AS CourseMember
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN CourseMember.firstNames
      |                WHEN 3 THEN CourseMember.lastName
      |                ELSE ''
      |            END ASC,
      |            CASE(?)
      |                WHEN 2 THEN CourseMember.firstNames
      |                WHEN 4 THEN CourseMember.lastName
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN CourseMember.earliestJoinDate
      |                WHEN 9 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN CourseMember.earliestJoinDate
      |                WHEN 10 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END DESC
      |    ) AS _PagingData LIMIT ? OFFSET ?
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,clazzUid)
      _stmt.setInt(3,roleId)
      _stmt.setInt(4,filter)
      _stmt.setLong(5,currentTime)
      _stmt.setLong(6,clazzUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,accountPersonUid)
      _stmt.setLong(9,clazzUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,accountPersonUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setString(13,searchText)
      _stmt.setInt(14,sortOrder)
      _stmt.setInt(15,sortOrder)
      _stmt.setInt(16,sortOrder)
      _stmt.setInt(17,sortOrder)
      _stmt.setInt(18,_limit)
      _stmt.setInt(19,_offset)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapRows {
          val _tmp_earliestJoinDate = _result.getLong("earliestJoinDate")
          val _tmp_latestDateLeft = _result.getLong("latestDateLeft")
          val _tmp_enrolmentRole = _result.getInt("enrolmentRole")
          var _tmp_Person_nullCount = 0
          val _tmp_personUid = _result.getLong("personUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_username = _result.getString("username")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_firstNames = _result.getString("firstNames")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_lastName = _result.getString("lastName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_emailAddr = _result.getString("emailAddr")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_phoneNum = _result.getString("phoneNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_gender = _result.getInt("gender")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_active = _result.getBoolean("active")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personAddress = _result.getString("personAddress")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personOrgId = _result.getString("personOrgId")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personGroupUid = _result.getLong("personGroupUid")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLct = _result.getLong("personLct")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personCountry = _result.getString("personCountry")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personType = _result.getInt("personType")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_admin = _result.getBoolean("admin")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_personNotes = _result.getString("personNotes")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherName = _result.getString("fatherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_fatherNumber = _result.getString("fatherNumber")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherName = _result.getString("motherName")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_motherNum = _result.getString("motherNum")
          if(_result.wasNull()) _tmp_Person_nullCount++
          val _tmp_Person_isAllNull = _tmp_Person_nullCount == 24
          var _tmp_PersonPicture_nullCount = 0
          val _tmp_personPictureUid = _result.getLong("personPictureUid")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureLct = _result.getLong("personPictureLct")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureUri = _result.getString("personPictureUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_fileSize = _result.getInt("fileSize")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
          if(_result.wasNull()) _tmp_PersonPicture_nullCount++
          val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
          PersonAndClazzMemberListDetails().apply {
            this.earliestJoinDate = _tmp_earliestJoinDate
            this.latestDateLeft = _tmp_latestDateLeft
            this.enrolmentRole = _tmp_enrolmentRole
            if(!_tmp_Person_isAllNull) {
              this.person = Person().apply {
                this.personUid = _tmp_personUid
                this.username = _tmp_username
                this.firstNames = _tmp_firstNames
                this.lastName = _tmp_lastName
                this.emailAddr = _tmp_emailAddr
                this.phoneNum = _tmp_phoneNum
                this.gender = _tmp_gender
                this.active = _tmp_active
                this.dateOfBirth = _tmp_dateOfBirth
                this.personAddress = _tmp_personAddress
                this.personOrgId = _tmp_personOrgId
                this.personGroupUid = _tmp_personGroupUid
                this.personLct = _tmp_personLct
                this.personCountry = _tmp_personCountry
                this.personType = _tmp_personType
                this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
                this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
                this.personLastChangedBy = _tmp_personLastChangedBy
                this.admin = _tmp_admin
                this.personNotes = _tmp_personNotes
                this.fatherName = _tmp_fatherName
                this.fatherNumber = _tmp_fatherNumber
                this.motherName = _tmp_motherName
                this.motherNum = _tmp_motherNum
              }
            }
            if(!_tmp_PersonPicture_isAllNull) {
              this.personPicture = PersonPicture().apply {
                this.personPictureUid = _tmp_personPictureUid
                this.personPictureLct = _tmp_personPictureLct
                this.personPictureUri = _tmp_personPictureUri
                this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
                this.fileSize = _tmp_fileSize
                this.personPictureActive = _tmp_personPictureActive
              }
            }
          }
        }
      }
    }

    override suspend fun countRows(): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
      sql = """
      |SELECT COUNT(*) FROM (
      |        SELECT * 
      |          FROM (SELECT Person.*, PersonPicture.*,
      |                       (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, 
      |        
      |                       (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, 
      |        
      |                       (SELECT ClazzEnrolment.clazzEnrolmentRole 
      |                          FROM ClazzEnrolment 
      |                         WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid 
      |                           AND ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive
      |                      ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC
      |                         LIMIT 1) AS enrolmentRole
      |                  FROM Person
      |                       LEFT JOIN PersonPicture
      |                                 ON PersonPicture.personPictureUid = Person.personUid
      |                       --Dummy join so that invalidations of the StatementEntity table will trigger
      |                       -- PagingSource invalidation of ClazzGradebookPagingSource
      |                       LEFT JOIN StatementEntity
      |                                 ON StatementEntity.statementIdHi = 0 
      |                                    AND StatementEntity.statementIdLo = 0
      |                       LEFT JOIN ActorEntity
      |                                 ON ActorEntity.actorUid = 0
      |                 WHERE Person.personUid IN 
      |                       (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid 
      |                          FROM ClazzEnrolment 
      |                         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ? 
      |                           AND ClazzEnrolment.clazzEnrolmentActive 
      |                           AND ClazzEnrolment.clazzEnrolmentRole = ? 
      |                           AND (? != 1 
      |                                 OR (? 
      |                                      BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
      |                                      AND ClazzEnrolment.clazzEnrolmentDateLeft))) 
      |                   /* Begin permission check */
      |                   AND (
      |                           ((
      |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
      |             (COALESCE(
      |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
      |                             FROM Clazz _Clazz_Permission
      |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
      |              /* 
      |              If there is a CoursePermission entity that is for the course as per the clazzUid
      |              parameter that is granted to the person directly or to the enrolmentRole that the 
      |              person has in the course, then permission is granted.
      |              */              
      |              OR EXISTS(SELECT CoursePermission.cpUid
      |                          FROM CoursePermission
      |                               
      |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
      |                        ON CoursePermission.cpToEnrolmentRole != 0
      |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
      |                           (SELECT COALESCE(
      |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
      |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
      |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
      |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
      |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
      |                                     LIMIT 1), 0))
      |    
      |                         WHERE CoursePermission.cpClazzUid = ?
      |                           AND (CoursePermission.cpToPersonUid = ? 
      |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
      |                           AND (CoursePermission.cpPermissionsFlag & 
      |         128
      |                            
      |        ) > 0)
      |              OR EXISTS(SELECT SystemPermission.spUid
      |                          FROM SystemPermission
      |                         WHERE SystemPermission.spToPersonUid = ?
      |                           AND (SystemPermission.spPermissionsFlag & 
      |     128
      |                            
      |        ) > 0)
      |               )
      |    )
      |                        OR Person.personUid = ?
      |                       )  
      |                   /* End permission check */                   
      |                   AND Person.firstNames || ' ' || Person.lastName LIKE ?
      |               GROUP BY Person.personUid, PersonPicture.personPictureUid) AS CourseMember
      |      ORDER BY CASE(?)
      |                WHEN 1 THEN CourseMember.firstNames
      |                WHEN 3 THEN CourseMember.lastName
      |                ELSE ''
      |            END ASC,
      |            CASE(?)
      |                WHEN 2 THEN CourseMember.firstNames
      |                WHEN 4 THEN CourseMember.lastName
      |                ELSE ''
      |            END DESC,
      |            CASE(?)
      |                WHEN 7 THEN CourseMember.earliestJoinDate
      |                WHEN 9 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END ASC,
      |            CASE(?)
      |                WHEN 8 THEN CourseMember.earliestJoinDate
      |                WHEN 10 THEN CourseMember.latestDateLeft
      |                ELSE 0
      |            END DESC
      |    ) AS _PagingCount
      """.trimMargin(),
      readOnly = true,)
    ) { _stmt -> 
      _stmt.setLong(1,clazzUid)
      _stmt.setLong(2,clazzUid)
      _stmt.setInt(3,roleId)
      _stmt.setInt(4,filter)
      _stmt.setLong(5,currentTime)
      _stmt.setLong(6,clazzUid)
      _stmt.setLong(7,accountPersonUid)
      _stmt.setLong(8,accountPersonUid)
      _stmt.setLong(9,clazzUid)
      _stmt.setLong(10,accountPersonUid)
      _stmt.setLong(11,accountPersonUid)
      _stmt.setLong(12,accountPersonUid)
      _stmt.setString(13,searchText)
      _stmt.setInt(14,sortOrder)
      _stmt.setInt(15,sortOrder)
      _stmt.setInt(16,sortOrder)
      _stmt.setInt(17,sortOrder)
      _stmt.executeQueryAsyncKmp().useResults{ _result -> 
        _result.mapNextRow(0) {
          _result.getInt(1)
        }
      }
    }
  }

  override suspend fun findActorEntitiesForGradebook(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
    studentsLimit: Int,
    studentsOffset: Int,
  ): List<ActorEntity> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |          WITH 
    |        PersonUids(personUid) AS (
    |            SELECT CourseMember.personUid 
    |              FROM (SELECT Person.*,
    |                           (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) 
    |                              FROM ClazzEnrolment 
    |                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, 
    |            
    |                           (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) 
    |                              FROM ClazzEnrolment 
    |                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, 
    |            
    |                           (SELECT ClazzEnrolment.clazzEnrolmentRole 
    |                              FROM ClazzEnrolment 
    |                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid 
    |                               AND ClazzEnrolment.clazzEnrolmentClazzUid = ? 
    |                               AND ClazzEnrolment.clazzEnrolmentActive
    |                          ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC
    |                             LIMIT 1) AS enrolmentRole
    |                      FROM Person
    |                     WHERE Person.personUid IN 
    |                           (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid 
    |                              FROM ClazzEnrolment 
    |                             WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ? 
    |                               AND ClazzEnrolment.clazzEnrolmentActive 
    |                               AND ClazzEnrolment.clazzEnrolmentRole = ? 
    |                               AND (? != 1 
    |                                     OR (? 
    |                                          BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
    |                                          AND ClazzEnrolment.clazzEnrolmentDateLeft))) 
    |                       /* Begin permission check */
    |                       AND (
    |                               ((
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = ?
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         128
    |                                
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     128
    |                                
    |        ) > 0)
    |               )
    |    )
    |                            OR Person.personUid = ?
    |                           )  
    |                       /* End permission check */                   
    |                       AND Person.firstNames || ' ' || Person.lastName LIKE ?
    |                   GROUP BY Person.personUid) AS CourseMember
    |          ORDER BY CASE(?)
    |                    WHEN 1 THEN CourseMember.firstNames
    |                    WHEN 3 THEN CourseMember.lastName
    |                    ELSE ''
    |                END ASC,
    |                CASE(?)
    |                    WHEN 2 THEN CourseMember.firstNames
    |                    WHEN 4 THEN CourseMember.lastName
    |                    ELSE ''
    |                END DESC,
    |                CASE(?)
    |                    WHEN 7 THEN CourseMember.earliestJoinDate
    |                    WHEN 9 THEN CourseMember.latestDateLeft
    |                    ELSE 0
    |                END ASC,
    |                CASE(?)
    |                    WHEN 8 THEN CourseMember.earliestJoinDate
    |                    WHEN 10 THEN CourseMember.latestDateLeft
    |                    ELSE 0
    |                END DESC
    |             LIMIT ?
    |            OFFSET ?   
    |         )
    |    
    |          
    |        
    |        SELECT ActorEntity.*
    |          FROM ActorEntity
    |         WHERE ActorEntity.actorPersonUid IN 
    |               (SELECT PersonUids.personUid
    |                  FROM PersonUids)
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setInt(3,roleId)
    _stmt.setInt(4,filter)
    _stmt.setLong(5,currentTime)
    _stmt.setLong(6,clazzUid)
    _stmt.setLong(7,accountPersonUid)
    _stmt.setLong(8,accountPersonUid)
    _stmt.setLong(9,clazzUid)
    _stmt.setLong(10,accountPersonUid)
    _stmt.setLong(11,accountPersonUid)
    _stmt.setLong(12,accountPersonUid)
    _stmt.setString(13,searchText)
    _stmt.setInt(14,sortOrder)
    _stmt.setInt(15,sortOrder)
    _stmt.setInt(16,sortOrder)
    _stmt.setInt(17,sortOrder)
    _stmt.setInt(18,studentsLimit)
    _stmt.setInt(19,studentsOffset)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_actorUid = _result.getLong("actorUid")
        val _tmp_actorPersonUid = _result.getLong("actorPersonUid")
        val _tmp_actorName = _result.getString("actorName")
        val _tmp_actorMbox = _result.getString("actorMbox")
        val _tmp_actorMbox_sha1sum = _result.getString("actorMbox_sha1sum")
        val _tmp_actorOpenid = _result.getString("actorOpenid")
        val _tmp_actorAccountName = _result.getString("actorAccountName")
        val _tmp_actorAccountHomePage = _result.getString("actorAccountHomePage")
        val _tmp_actorEtag = _result.getLong("actorEtag")
        val _tmp_actorLct = _result.getLong("actorLct")
        val _tmp_actorObjectType = _result.getInt("actorObjectType")
        ActorEntity().apply {
          this.actorUid = _tmp_actorUid
          this.actorPersonUid = _tmp_actorPersonUid
          this.actorName = _tmp_actorName
          this.actorMbox = _tmp_actorMbox
          this.actorMbox_sha1sum = _tmp_actorMbox_sha1sum
          this.actorOpenid = _tmp_actorOpenid
          this.actorAccountName = _tmp_actorAccountName
          this.actorAccountHomePage = _tmp_actorAccountHomePage
          this.actorEtag = _tmp_actorEtag
          this.actorLct = _tmp_actorLct
          this.actorObjectType = _tmp_actorObjectType
        }
      }
    }
  }

  override suspend fun findEnrolmentsAndPersonByClazzUidWithPermissionCheck(clazzUid: Long,
      accountPersonUid: Long): List<ClazzEnrolmentAndPerson> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |       SELECT ClazzEnrolment.*,
    |              Person.*,
    |              PersonPicture.*
    |         FROM ClazzEnrolment
    |              JOIN Person
    |                   ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid
    |              LEFT JOIN PersonPicture
    |                   ON PersonPicture.personPictureUid = ClazzEnrolment.clazzEnrolmentPersonUid
    |                   
    |        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |              /* Begin permission check*/
    |          AND (
    |                   ((
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = ?
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |        
    |                    8192
    |                    
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |    
    |                    8192
    |                    
    |        ) > 0)
    |               )
    |    )
    |              )  
    |              /* End permission check */
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,accountPersonUid)
    _stmt.setLong(5,clazzUid)
    _stmt.setLong(6,accountPersonUid)
    _stmt.setLong(7,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        var _tmp_Person_nullCount = 0
        val _tmp_personUid = _result.getLong("personUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_username = _result.getString("username")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_firstNames = _result.getString("firstNames")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_lastName = _result.getString("lastName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_emailAddr = _result.getString("emailAddr")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_phoneNum = _result.getString("phoneNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_gender = _result.getInt("gender")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_active = _result.getBoolean("active")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_dateOfBirth = _result.getLong("dateOfBirth")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personAddress = _result.getString("personAddress")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personOrgId = _result.getString("personOrgId")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personGroupUid = _result.getLong("personGroupUid")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLct = _result.getLong("personLct")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personCountry = _result.getString("personCountry")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personType = _result.getInt("personType")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personMasterChangeSeqNum = _result.getLong("personMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLocalChangeSeqNum = _result.getLong("personLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personLastChangedBy = _result.getInt("personLastChangedBy")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_admin = _result.getBoolean("admin")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_personNotes = _result.getString("personNotes")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherName = _result.getString("fatherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_fatherNumber = _result.getString("fatherNumber")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherName = _result.getString("motherName")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_motherNum = _result.getString("motherNum")
        if(_result.wasNull()) _tmp_Person_nullCount++
        val _tmp_Person_isAllNull = _tmp_Person_nullCount == 24
        var _tmp_ClazzEnrolment_nullCount = 0
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        if(_result.wasNull()) _tmp_ClazzEnrolment_nullCount++
        val _tmp_ClazzEnrolment_isAllNull = _tmp_ClazzEnrolment_nullCount == 14
        var _tmp_PersonPicture_nullCount = 0
        val _tmp_personPictureUid = _result.getLong("personPictureUid")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureLct = _result.getLong("personPictureLct")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureUri = _result.getString("personPictureUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureThumbnailUri = _result.getString("personPictureThumbnailUri")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_fileSize = _result.getInt("fileSize")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_personPictureActive = _result.getBoolean("personPictureActive")
        if(_result.wasNull()) _tmp_PersonPicture_nullCount++
        val _tmp_PersonPicture_isAllNull = _tmp_PersonPicture_nullCount == 6
        ClazzEnrolmentAndPerson().apply {
          if(!_tmp_Person_isAllNull) {
            this.person = Person().apply {
              this.personUid = _tmp_personUid
              this.username = _tmp_username
              this.firstNames = _tmp_firstNames
              this.lastName = _tmp_lastName
              this.emailAddr = _tmp_emailAddr
              this.phoneNum = _tmp_phoneNum
              this.gender = _tmp_gender
              this.active = _tmp_active
              this.dateOfBirth = _tmp_dateOfBirth
              this.personAddress = _tmp_personAddress
              this.personOrgId = _tmp_personOrgId
              this.personGroupUid = _tmp_personGroupUid
              this.personLct = _tmp_personLct
              this.personCountry = _tmp_personCountry
              this.personType = _tmp_personType
              this.personMasterChangeSeqNum = _tmp_personMasterChangeSeqNum
              this.personLocalChangeSeqNum = _tmp_personLocalChangeSeqNum
              this.personLastChangedBy = _tmp_personLastChangedBy
              this.admin = _tmp_admin
              this.personNotes = _tmp_personNotes
              this.fatherName = _tmp_fatherName
              this.fatherNumber = _tmp_fatherNumber
              this.motherName = _tmp_motherName
              this.motherNum = _tmp_motherNum
            }
          }
          if(!_tmp_ClazzEnrolment_isAllNull) {
            this.enrolment = ClazzEnrolment().apply {
              this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
              this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
              this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
              this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
              this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
              this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
              this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
              this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
              this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
              this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
              this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
              this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
              this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
              this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
            }
          }
          if(!_tmp_PersonPicture_isAllNull) {
            this.picture = PersonPicture().apply {
              this.personPictureUid = _tmp_personPictureUid
              this.personPictureLct = _tmp_personPictureLct
              this.personPictureUri = _tmp_personPictureUri
              this.personPictureThumbnailUri = _tmp_personPictureThumbnailUri
              this.fileSize = _tmp_fileSize
              this.personPictureActive = _tmp_personPictureActive
            }
          }
        }
      }
    }
  }

  override suspend fun findByAccountPersonUidAndClazzUid(accountPersonUid: Long, clazzUid: Long):
      List<ClazzEnrolment> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ClazzEnrolment.*
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |           AND ClazzEnrolment.clazzEnrolmentPersonUid = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
        }
      }
    }
  }

  override suspend fun findEnrolmentsByClazzUidAndRole(
    clazzUid: Long,
    accountPersonUid: Long,
    roleId: Int,
    permission: Long,
  ): List<ClazzEnrolment> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |       SELECT ClazzEnrolment.*
    |         FROM ClazzEnrolment
    |        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |          AND ClazzEnrolment.clazzEnrolmentRole = ?
    |              /* Begin permission check*/
    |          AND (
    |                   (
    |         (
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = ?
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         ?
    |         
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     ?
    |         
    |        ) > 0)
    |               )
    |    
    |    )
    |                OR ClazzEnrolment.clazzEnrolmentPersonUid = ?
    |              )  
    |              /* End permission check */
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setInt(2,roleId)
    _stmt.setLong(3,clazzUid)
    _stmt.setLong(4,accountPersonUid)
    _stmt.setLong(5,accountPersonUid)
    _stmt.setLong(6,clazzUid)
    _stmt.setLong(7,accountPersonUid)
    _stmt.setLong(8,permission)
    _stmt.setLong(9,accountPersonUid)
    _stmt.setLong(10,permission)
    _stmt.setLong(11,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
        }
      }
    }
  }

  override suspend fun findEnrolmentsByClazzUidAndRolePaged(
    clazzUid: Long,
    roleId: Int,
    sortOrder: Int,
    searchText: String?,
    filter: Int,
    accountPersonUid: Long,
    currentTime: Long,
    permission: Long,
    studentsLimit: Int,
    studentsOffset: Int,
  ): List<ClazzEnrolment> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |         WITH 
    |        PersonUids(personUid) AS (
    |            SELECT CourseMember.personUid 
    |              FROM (SELECT Person.*,
    |                           (SELECT MIN(ClazzEnrolment.clazzEnrolmentDateJoined) 
    |                              FROM ClazzEnrolment 
    |                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS earliestJoinDate, 
    |            
    |                           (SELECT MAX(ClazzEnrolment.clazzEnrolmentDateLeft) 
    |                              FROM ClazzEnrolment 
    |                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid) AS latestDateLeft, 
    |            
    |                           (SELECT ClazzEnrolment.clazzEnrolmentRole 
    |                              FROM ClazzEnrolment 
    |                             WHERE Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid 
    |                               AND ClazzEnrolment.clazzEnrolmentClazzUid = ? 
    |                               AND ClazzEnrolment.clazzEnrolmentActive
    |                          ORDER BY ClazzEnrolment.clazzEnrolmentDateLeft DESC
    |                             LIMIT 1) AS enrolmentRole
    |                      FROM Person
    |                     WHERE Person.personUid IN 
    |                           (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid 
    |                              FROM ClazzEnrolment 
    |                             WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ? 
    |                               AND ClazzEnrolment.clazzEnrolmentActive 
    |                               AND ClazzEnrolment.clazzEnrolmentRole = ? 
    |                               AND (? != 1 
    |                                     OR (? 
    |                                          BETWEEN ClazzEnrolment.clazzEnrolmentDateJoined 
    |                                          AND ClazzEnrolment.clazzEnrolmentDateLeft))) 
    |                       /* Begin permission check */
    |                       AND (
    |                               ((
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = ?
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         128
    |                                
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     128
    |                                
    |        ) > 0)
    |               )
    |    )
    |                            OR Person.personUid = ?
    |                           )  
    |                       /* End permission check */                   
    |                       AND Person.firstNames || ' ' || Person.lastName LIKE ?
    |                   GROUP BY Person.personUid) AS CourseMember
    |          ORDER BY CASE(?)
    |                    WHEN 1 THEN CourseMember.firstNames
    |                    WHEN 3 THEN CourseMember.lastName
    |                    ELSE ''
    |                END ASC,
    |                CASE(?)
    |                    WHEN 2 THEN CourseMember.firstNames
    |                    WHEN 4 THEN CourseMember.lastName
    |                    ELSE ''
    |                END DESC,
    |                CASE(?)
    |                    WHEN 7 THEN CourseMember.earliestJoinDate
    |                    WHEN 9 THEN CourseMember.latestDateLeft
    |                    ELSE 0
    |                END ASC,
    |                CASE(?)
    |                    WHEN 8 THEN CourseMember.earliestJoinDate
    |                    WHEN 10 THEN CourseMember.latestDateLeft
    |                    ELSE 0
    |                END DESC
    |             LIMIT ?
    |            OFFSET ?   
    |         )
    |    
    |        
    |       SELECT ClazzEnrolment.*
    |         FROM ClazzEnrolment
    |        WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |          AND ClazzEnrolment.clazzEnrolmentRole = ?
    |          AND ClazzEnrolment.clazzEnrolmentPersonUid IN (
    |              SELECT PersonUids.personUid
    |                FROM PersonUids)
    |              /* Begin permission check*/
    |          AND (
    |                   (
    |         (
    |             /* If the accountPersonUid is the owner of the course, all permissions are granted */
    |             (COALESCE(
    |                          (SELECT _Clazz_Permission.clazzOwnerPersonUid 
    |                             FROM Clazz _Clazz_Permission
    |                            WHERE _Clazz_Permission.clazzUid = ?), 0) = ?)
    |              /* 
    |              If there is a CoursePermission entity that is for the course as per the clazzUid
    |              parameter that is granted to the person directly or to the enrolmentRole that the 
    |              person has in the course, then permission is granted.
    |              */              
    |              OR EXISTS(SELECT CoursePermission.cpUid
    |                          FROM CoursePermission
    |                               
    |        LEFT JOIN ClazzEnrolment ClazzEnrolment_ForAccountPerson 
    |                        ON CoursePermission.cpToEnrolmentRole != 0
    |                       AND ClazzEnrolment_ForAccountPerson.clazzEnrolmentUid = 
    |                           (SELECT COALESCE(
    |                                   (SELECT _ClazzEnrolment_AccountPersonInner.clazzEnrolmentUid 
    |                                      FROM ClazzEnrolment _ClazzEnrolment_AccountPersonInner
    |                                     WHERE _ClazzEnrolment_AccountPersonInner.clazzEnrolmentClazzUid = CoursePermission.cpClazzUid
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentPersonUid = ?
    |                                       AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive
    |                                  ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC   
    |                                     LIMIT 1), 0))
    |    
    |                         WHERE CoursePermission.cpClazzUid = ?
    |                           AND (CoursePermission.cpToPersonUid = ? 
    |                                OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole)
    |                           AND (CoursePermission.cpPermissionsFlag & 
    |         ?
    |         
    |        ) > 0)
    |              OR EXISTS(SELECT SystemPermission.spUid
    |                          FROM SystemPermission
    |                         WHERE SystemPermission.spToPersonUid = ?
    |                           AND (SystemPermission.spPermissionsFlag & 
    |     ?
    |         
    |        ) > 0)
    |               )
    |    
    |    )
    |                OR ClazzEnrolment.clazzEnrolmentPersonUid = ?
    |              )  
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,clazzUid)
    _stmt.setInt(3,roleId)
    _stmt.setInt(4,filter)
    _stmt.setLong(5,currentTime)
    _stmt.setLong(6,clazzUid)
    _stmt.setLong(7,accountPersonUid)
    _stmt.setLong(8,accountPersonUid)
    _stmt.setLong(9,clazzUid)
    _stmt.setLong(10,accountPersonUid)
    _stmt.setLong(11,accountPersonUid)
    _stmt.setLong(12,accountPersonUid)
    _stmt.setString(13,searchText)
    _stmt.setInt(14,sortOrder)
    _stmt.setInt(15,sortOrder)
    _stmt.setInt(16,sortOrder)
    _stmt.setInt(17,sortOrder)
    _stmt.setInt(18,studentsLimit)
    _stmt.setInt(19,studentsOffset)
    _stmt.setLong(20,clazzUid)
    _stmt.setInt(21,roleId)
    _stmt.setLong(22,clazzUid)
    _stmt.setLong(23,accountPersonUid)
    _stmt.setLong(24,accountPersonUid)
    _stmt.setLong(25,clazzUid)
    _stmt.setLong(26,accountPersonUid)
    _stmt.setLong(27,permission)
    _stmt.setLong(28,accountPersonUid)
    _stmt.setLong(29,permission)
    _stmt.setLong(30,accountPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
        }
      }
    }
  }

  override suspend fun findAllEnrolmentsByClazzUidAndRole(clazzUid: Long, roleId: Int):
      List<ClazzEnrolment> = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT ClazzEnrolment.*
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentClazzUid = ?
    |           AND ClazzEnrolment.clazzEnrolmentRole = ?
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setInt(2,roleId)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
        }
      }
    }
  }

  override fun updateClazzEnrolmentActiveForClazzEnrolment(
    clazzEnrolmentUid: Long,
    enrolled: Boolean,
    timeChanged: Long,
  ): Int = _db.prepareAndUseStatement(PreparedStatementConfig(
    sql = """
    |
    |        UPDATE ClazzEnrolment 
    |          SET clazzEnrolmentActive = ?,
    |              clazzEnrolmentLct = ?
    |        WHERE clazzEnrolmentUid = ?
    """.trimMargin(),
    readOnly = false,)
  ) { _stmt -> 
    _stmt.setBoolean(1,enrolled)
    _stmt.setLong(2,timeChanged)
    _stmt.setLong(3,clazzEnrolmentUid)
    _stmt.executeUpdate()
  }

  override suspend fun updateClazzEnrolmentRole(
    personUid: Long,
    clazzUid: Long,
    newRole: Int,
    oldRole: Int,
    updateTime: Long,
  ): Int = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |            UPDATE ClazzEnrolment 
    |               SET clazzEnrolmentRole = ?,
    |                   clazzEnrolmentLct = ?      
    |             -- Avoid potential for duplicate approvals if user was previously refused      
    |             WHERE clazzEnrolmentUid = COALESCE( 
    |                    (SELECT clazzEnrolmentUid
    |                       FROM ClazzEnrolment
    |                      WHERE clazzEnrolmentPersonUid = ? 
    |                            AND clazzEnrolmentClazzUid = ?
    |                            AND clazzEnrolmentRole = ?
    |                            AND CAST(clazzEnrolmentActive AS INTEGER) = 1
    |                      LIMIT 1), 0)
    """.trimMargin(),
    readOnly = false,)
  ) { _stmt -> 
    _stmt.setInt(1,newRole)
    _stmt.setLong(2,updateTime)
    _stmt.setLong(3,personUid)
    _stmt.setLong(4,clazzUid)
    _stmt.setInt(5,oldRole)
    _stmt.executeUpdateAsyncKmp()
  }

  override suspend fun getClazzNameAndPersonName(personUid: Long, clazzUid: Long):
      CourseNameAndPersonName? = _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |        SELECT Person.firstNames, Person.lastName, Clazz.clazzName
    |          FROM Person
    |               LEFT JOIN Clazz
    |                         ON Clazz.clazzUid = ?
    |        WHERE Person.personUid = ?                 
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,clazzUid)
    _stmt.setLong(2,personUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapNextRow(null) {
        val _tmp_clazzName = _result.getString("clazzName")
        val _tmp_firstNames = _result.getString("firstNames")
        val _tmp_lastName = _result.getString("lastName")
        CourseNameAndPersonName().apply {
          this.clazzName = _tmp_clazzName
          this.firstNames = _tmp_firstNames
          this.lastName = _tmp_lastName
        }
      }
    }
  }

  override suspend
      fun findClazzEnrolmentEntitiesForPersonViewPermissionCheck(accountPersonUid: Long,
      otherPersonUid: Long): List<ClazzEnrolment> =
      _db.prepareAndUseStatementAsync(PreparedStatementConfig(
    sql = """
    |
    |          WITH CanViewMembersClazzesViaCoursePermission(clazzUid) AS
    |               /* Get clazzuids where active user can view members based on their own enrolment role */
    |               (SELECT CoursePermission.cpClazzUid
    |                  FROM ClazzEnrolment ClazzEnrolment_ActiveUser
    |                       JOIN CoursePermission 
    |                            ON CoursePermission.cpClazzUid = ClazzEnrolment_ActiveUser.clazzEnrolmentClazzUid
    |                           AND CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ActiveUser.clazzEnrolmentRole
    |                 WHERE ClazzEnrolment_ActiveUser.clazzEnrolmentPersonUid = ? 
    |                   AND (CoursePermission.cpPermissionsFlag & 8192) > 0 
    |                UNION
    |                /* Get ClazzUids where the active user can view members based a grant directly to them */
    |                SELECT CoursePermission.cpClazzUid
    |                  FROM CoursePermission
    |                 WHERE CoursePermission.cpToPersonUid  = ?
    |                   AND (CoursePermission.cpPermissionsFlag & 8192) > 0
    |               )
    |        SELECT ClazzEnrolment.*
    |          FROM ClazzEnrolment
    |         WHERE ClazzEnrolment.clazzEnrolmentPersonUid = ?
    |            OR (    ClazzEnrolment.clazzEnrolmentClazzUid IN 
    |                        (SELECT CanViewMembersClazzesViaCoursePermission.clazzUid
    |                           FROM CanViewMembersClazzesViaCoursePermission)
    |                AND (? = 0 OR ClazzEnrolment.clazzEnrolmentPersonUid = ?)   
    |                )
    |    
    """.trimMargin(),
    readOnly = true,)
  ) { _stmt -> 
    _stmt.setLong(1,accountPersonUid)
    _stmt.setLong(2,accountPersonUid)
    _stmt.setLong(3,accountPersonUid)
    _stmt.setLong(4,otherPersonUid)
    _stmt.setLong(5,otherPersonUid)
    _stmt.executeQueryAsyncKmp().useResults{ _result -> 
      _result.mapRows {
        val _tmp_clazzEnrolmentUid = _result.getLong("clazzEnrolmentUid")
        val _tmp_clazzEnrolmentPersonUid = _result.getLong("clazzEnrolmentPersonUid")
        val _tmp_clazzEnrolmentClazzUid = _result.getLong("clazzEnrolmentClazzUid")
        val _tmp_clazzEnrolmentDateJoined = _result.getLong("clazzEnrolmentDateJoined")
        val _tmp_clazzEnrolmentDateLeft = _result.getLong("clazzEnrolmentDateLeft")
        val _tmp_clazzEnrolmentRole = _result.getInt("clazzEnrolmentRole")
        val _tmp_clazzEnrolmentAttendancePercentage =
            _result.getFloat("clazzEnrolmentAttendancePercentage")
        val _tmp_clazzEnrolmentActive = _result.getBoolean("clazzEnrolmentActive")
        val _tmp_clazzEnrolmentLeavingReasonUid = _result.getLong("clazzEnrolmentLeavingReasonUid")
        val _tmp_clazzEnrolmentOutcome = _result.getInt("clazzEnrolmentOutcome")
        val _tmp_clazzEnrolmentLocalChangeSeqNum =
            _result.getLong("clazzEnrolmentLocalChangeSeqNum")
        val _tmp_clazzEnrolmentMasterChangeSeqNum =
            _result.getLong("clazzEnrolmentMasterChangeSeqNum")
        val _tmp_clazzEnrolmentLastChangedBy = _result.getInt("clazzEnrolmentLastChangedBy")
        val _tmp_clazzEnrolmentLct = _result.getLong("clazzEnrolmentLct")
        ClazzEnrolment().apply {
          this.clazzEnrolmentUid = _tmp_clazzEnrolmentUid
          this.clazzEnrolmentPersonUid = _tmp_clazzEnrolmentPersonUid
          this.clazzEnrolmentClazzUid = _tmp_clazzEnrolmentClazzUid
          this.clazzEnrolmentDateJoined = _tmp_clazzEnrolmentDateJoined
          this.clazzEnrolmentDateLeft = _tmp_clazzEnrolmentDateLeft
          this.clazzEnrolmentRole = _tmp_clazzEnrolmentRole
          this.clazzEnrolmentAttendancePercentage = _tmp_clazzEnrolmentAttendancePercentage
          this.clazzEnrolmentActive = _tmp_clazzEnrolmentActive
          this.clazzEnrolmentLeavingReasonUid = _tmp_clazzEnrolmentLeavingReasonUid
          this.clazzEnrolmentOutcome = _tmp_clazzEnrolmentOutcome
          this.clazzEnrolmentLocalChangeSeqNum = _tmp_clazzEnrolmentLocalChangeSeqNum
          this.clazzEnrolmentMasterChangeSeqNum = _tmp_clazzEnrolmentMasterChangeSeqNum
          this.clazzEnrolmentLastChangedBy = _tmp_clazzEnrolmentLastChangedBy
          this.clazzEnrolmentLct = _tmp_clazzEnrolmentLct
        }
      }
    }
  }
}
