1/home/jenkins/.jenkins/workspace/Release-Nayestane/lib-database/src/commonMain/kotlin/com/ustadmobile/core/db/dao/ClazzAssignmentDaoCommon.ktISELECT_PERSONUID_IF_ENROLLED_ELSE_0_FOR_PERSONUID_AND_ASSIGNMENTUID_SQL_1$thisT_get_SELECT_PERSONUID_IF_ENROLLED_ELSE_0_FOR_PERSONUID_AND_ASSIGNMENTUID_SQL__ap22ax,SELECT_GROUPSET_UID_FOR_ASSIGNMENT_UID_SQL_17_get_SELECT_GROUPSET_UID_FOR_ASSIGNMENT_UID_SQL__xqzhq8!ClazzAssignmentDaoCommon_instance+ SELECT COALESCE( (SELECT ClazzEnrolment.clazzEnrolmentPersonUid FROM ClazzEnrolment WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 AND ClazzEnrolment.clazzEnrolmentClazzUid = (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) LIMIT 1), 0) :SELECT_SUBMITTER_UID_FOR_PERSONUID_AND_ASSIGNMENTUID_SQL_1 SELECT CASE -- When assignment is individual then the submitter uid is the personuid if they are enrolled in the course otherwise zero WHEN (SELECT caGroupUid FROM ClazzAssignment WHERE caUid = :assignmentUid) = 0 THEN ( SELECT COALESCE( (SELECT ClazzEnrolment.clazzEnrolmentPersonUid FROM ClazzEnrolment WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 AND ClazzEnrolment.clazzEnrolmentClazzUid = (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) LIMIT 1), 0) ) -- When assignment is by groups but the active user is not an enrolled student then the submitter uid is zero WHEN (SELECT caGroupUid FROM ClazzAssignment WHERE caUid = :assignmentUid) != 0 AND ( SELECT COALESCE( (SELECT ClazzEnrolment.clazzEnrolmentPersonUid FROM ClazzEnrolment WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 AND ClazzEnrolment.clazzEnrolmentClazzUid = (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) LIMIT 1), 0) ) = 0 THEN 0 -- When assignment is by groups and the person is an enrolled student the submitter uid is the -- group that they are assigned to. If they are not assigned to a group but are enrolled -- then we submitter uid = SUBMITTER_ENROLLED_BUT_NOT_IN_GROUP ELSE COALESCE( (SELECT CourseGroupMember.cgmGroupNumber FROM CourseGroupMember WHERE ( SELECT COALESCE( (SELECT ClazzEnrolment.clazzEnrolmentPersonUid FROM ClazzEnrolment WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 AND ClazzEnrolment.clazzEnrolmentClazzUid = (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) LIMIT 1), 0) ) > 0 AND CourseGroupMember.cgmSetUid = (SELECT caGroupUid FROM ClazzAssignment WHERE caUid = :assignmentUid) AND CourseGroupMember.cgmPersonUid = :accountPersonUid LIMIT 1), -1) END 'SUBMITTER_LIST_WITHOUT_ASSIGNMENT_CTE_1 WITH SubmitterList (submitterId, name) AS (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, Person.firstNames || ' ' || Person.lastName AS name FROM ClazzEnrolment JOIN Person ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid WHERE :groupUid = 0 AND clazzEnrolmentClazzUid = :clazzUid AND clazzEnrolmentActive AND clazzEnrolmentRole = 1000 GROUP BY submitterId, name UNION SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId, :group || ' ' || CourseGroupMember.cgmGroupNumber AS name FROM CourseGroupMember JOIN CourseGroupSet ON CourseGroupSet.cgsUid = :groupUid WHERE CourseGroupMember.cgmSetUid = CourseGroupSet.cgsUid AND CourseGroupMember.cgmGroupNumber != 0 GROUP BY submitterId, name ) 7HAS_LEARNINGRECORD_AND_MEMBER_VIEW_PERMISSION_CTE_SQL_1 " HasLearningRecordSelectPermission (hasPermission) AS (SELECT ( ( /* 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 = :clazzUid), 0) = :accountPersonUid) /* 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 = :accountPersonUid AND _ClazzEnrolment_AccountPersonInner.clazzEnrolmentActive ORDER BY _ClazzEnrolment_AccountPersonInner.clazzEnrolmentDateLeft DESC LIMIT 1), 0)) WHERE CoursePermission.cpClazzUid = :clazzUid AND (CoursePermission.cpToPersonUid = :accountPersonUid OR CoursePermission.cpToEnrolmentRole = ClazzEnrolment_ForAccountPerson.clazzEnrolmentRole) AND (CoursePermission.cpPermissionsFlag & 128 ) > 0) OR EXISTS(SELECT SystemPermission.spUid FROM SystemPermission WHERE SystemPermission.spToPersonUid = :accountPersonUid AND (SystemPermission.spPermissionsFlag & 128 ) > 0) ) )) ASSIGNMENT_CLAZZ_UID_CTE_SQL_1 AssignmentClazzUid(clazzUid) AS (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) @SELECT_SUBMITTER_UID_FOR_ACCOUNT_PERSON_UID_AND_ASSIGNMENT_CTE_1  AccountSubmitterUid(accountSubmitterUid) AS ( SELECT CASE -- When assignment is individual then the submitter uid is the personuid if they are enrolled in the course otherwise zero WHEN (SELECT caGroupUid FROM ClazzAssignment WHERE caUid = :assignmentUid) = 0 THEN ( SELECT COALESCE( (SELECT ClazzEnrolment.clazzEnrolmentPersonUid FROM ClazzEnrolment WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 AND ClazzEnrolment.clazzEnrolmentClazzUid = (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) LIMIT 1), 0) ) -- When assignment is by groups but the active user is not an enrolled student then the submitter uid is zero WHEN (SELECT caGroupUid FROM ClazzAssignment WHERE caUid = :assignmentUid) != 0 AND ( SELECT COALESCE( (SELECT ClazzEnrolment.clazzEnrolmentPersonUid FROM ClazzEnrolment WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 AND ClazzEnrolment.clazzEnrolmentClazzUid = (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) LIMIT 1), 0) ) = 0 THEN 0 -- When assignment is by groups and the person is an enrolled student the submitter uid is the -- group that they are assigned to. If they are not assigned to a group but are enrolled -- then we submitter uid = SUBMITTER_ENROLLED_BUT_NOT_IN_GROUP ELSE COALESCE( (SELECT CourseGroupMember.cgmGroupNumber FROM CourseGroupMember WHERE ( SELECT COALESCE( (SELECT ClazzEnrolment.clazzEnrolmentPersonUid FROM ClazzEnrolment WHERE ClazzEnrolment.clazzEnrolmentPersonUid = :accountPersonUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 AND ClazzEnrolment.clazzEnrolmentClazzUid = (SELECT ClazzAssignment.caClazzUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) LIMIT 1), 0) ) > 0 AND CourseGroupMember.cgmSetUid = (SELECT caGroupUid FROM ClazzAssignment WHERE caUid = :assignmentUid) AND CourseGroupMember.cgmPersonUid = :accountPersonUid LIMIT 1), -1) END ) SELECT ClazzAssignment.caGroupUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid %SELECT_ASSIGNMENT_IS_PEERMARKED_SQL_1 ((SELECT ClazzAssignment.caMarkingType FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) = 2) SUBMITTER_LIST_CTE2_SQL_1` SubmitterList(submitterId, name) AS -- List of submitter uids and names if individual assignment eg caGroupUid = 0 (SELECT DISTINCT ClazzEnrolment.clazzEnrolmentPersonUid AS submitterId, Person.firstNames || ' ' || Person.lastName AS name FROM ClazzEnrolment JOIN Person ON Person.personUid = ClazzEnrolment.clazzEnrolmentPersonUid WHERE ( SELECT ClazzAssignment.caGroupUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid ) = 0 AND ClazzEnrolment.clazzEnrolmentClazzUid = :clazzUid AND ClazzEnrolment.clazzEnrolmentRole = 1000 -- either the active user has learnign record select permission on class or is an assigned reviewer for submitter AND ( (SELECT hasPermission FROM HasLearningRecordSelectPermission) OR -- check if the active person eg accountpersonuid is assigned to mark this peer ( ((SELECT ClazzAssignment.caMarkingType FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) = 2) AND EXISTS(SELECT PeerReviewerAllocation.praUid FROM PeerReviewerAllocation WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid AND PeerReviewerAllocation.praToMarkerSubmitterUid = ClazzEnrolment.clazzEnrolmentPersonUid AND PeerReviewerAllocation.praMarkerSubmitterUid = :accountPersonUid)) ) UNION -- List of submitter uids and names if the assignment is submitted by groups e.g. caGroupUid != 0 SELECT DISTINCT CourseGroupMember.cgmGroupNumber AS submitterId, :group || ' ' || CourseGroupMember.cgmGroupNumber AS name FROM CourseGroupMember WHERE ( SELECT ClazzAssignment.caGroupUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid ) != 0 AND CourseGroupMember.cgmSetUid = ( SELECT ClazzAssignment.caGroupUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid ) -- either the active user has learning record select permission on class or is an assigned reviewer for submitter AND ( (SELECT hasPermission FROM HasLearningRecordSelectPermission) OR --check if the active user is in a group that was allocated to do a peer review of the given submitter uid ( ((SELECT ClazzAssignment.caMarkingType FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid) = 2) AND EXISTS(SELECT PeerReviewerAllocation.praUid FROM PeerReviewerAllocation WHERE PeerReviewerAllocation.praAssignmentUid = :assignmentUid AND PeerReviewerAllocation.praToMarkerSubmitterUid = CourseGroupMember.cgmGroupNumber AND PeerReviewerAllocation.praMarkerSubmitterUid = (SELECT CourseGroupMemberInner.cgmGroupNumber FROM CourseGroupMember CourseGroupMemberInner WHERE CourseGroupMemberInner.cgmSetUid = ( SELECT ClazzAssignment.caGroupUid FROM ClazzAssignment WHERE ClazzAssignment.caUid = :assignmentUid ) AND CourseGroupMemberInner.cgmPersonUid = :accountPersonUid LIMIT 1) )) ) ) SORT_NAME_ASC_1SORT_NAME_DESC_1ClazzAssignmentDaoCommonFget_SELECT_SUBMITTER_UID_FOR_PERSONUID_AND_ASSIGNMENTUID_SQL_3pm0z5_k$protoOf3get_SUBMITTER_LIST_WITHOUT_ASSIGNMENT_CTE_ta54xn_k$Cget_HAS_LEARNINGRECORD_AND_MEMBER_VIEW_PERMISSION_CTE_SQL_cl36su_k$*get_ASSIGNMENT_CLAZZ_UID_CTE_SQL_y7nmfw_k$Lget_SELECT_SUBMITTER_UID_FOR_ACCOUNT_PERSON_UID_AND_ASSIGNMENT_CTE_ou89rw_k$1get_SELECT_ASSIGNMENT_IS_PEERMARKED_SQL_6pbvei_k$%get_SUBMITTER_LIST_CTE2_SQL_n9l2sv_k$get_SORT_NAME_ASC_byiy2d_k$get_SORT_NAME_DESC_fksp65_k$$ClazzAssignmentDaoCommon_getInstanceic#73:com.ustadmobile.core.db.dao/ClazzAssignmentDaoCommon.SELECT_PERSONUID_IF_ENROLLED_ELSE_0_FOR_PERSONUID_AND_ASSIGNMENTUID_SQL.|6726289951647177085[0]-0ic#73:com.ustadmobile.core.db.dao/ClazzAssignmentDaoCommon.SELECT_GROUPSET_UID_FOR_ASSIGNMENT_UID_SQL.|1771847918688368252[0]-0