[
{"createdChannel":null,"createdVenture":null,"isActive":true,"appUpdatedDateTime":null,"inActiveReason":null,"createdTenant":null,"Encoding":"UTF-8","Title":"[{\"languageCode\":\"ENG\",\"text\":\"ClassStudent_Table\"}]","createdIE":null,"updatedVenture":0,"appCreatedDateTime":null,"createdDateTime":"2020-09-30T18:38:24","updatedIE":0,"aliasId":null,"updatedDateTime":"2023-05-25T16:11:29","ContentType":"text/plain","updatedChannel":null,"inActiveDateTime":null,"updatedUser":1,"Description":"[{\"languageCode\":\"ENG\",\"text\":\"ClassStudent_Table\"}]","ResultProcessor":null,"Tenant":1,"DataSource":1,"Version":1,"notes":"Created using SO","Name":"ClassStudent_Table","updatedTenant":1,"iconpic":null,"DataSetType":"DATASET_TYPE.SQL","Configuration":"{\"queryText\":\"SELECT\\n    replace(\\n        min(st.studentSysGenCode),\\n        concat(min(s.id), '_', min(sclb.id), '_'),\\n        ''\\n    ) AS 'Student_Role_No',\\n    min(st.id) as 'Student_ID',\\n    min(p.id) as 'Person_ID',\\n    min(\\n        CONCAT(pp.firstName, ' ', ifnull(pp.lastname, ''))\\n    ) AS 'Student_Name',\\n    min(\\n        CASE\\n            WHEN Gender = 'GENDER.MALE' THEN 'Male'\\n            WHEN Gender = 'GENDER.FEMALE' THEN 'Female'\\n        END\\n    ) AS 'Gender',\\n    min(untxt.text) AS 'School_Group',\\n    min(schoolmltext.text) AS 'School',\\n    min(lntxt.text) as 'Class',\\n    min(batchname.text) as 'Classroom',\\n    min(\\n        case\\n            when st.isactive = 1 then 'Active'\\n            when st.isactive = 0 then 'Inactive'\\n        end\\n    ) as 'Active_Status',\\n    count(distinct cm_ac.id) as 'Community_Count',\\n    min(sclb.AcademicYear) as 'Batch',\\n    min(cntxt.text) as 'Course',\\n    min(\\n        case\\n            when bs.isActive = 1 then 'Active'\\n            when bs.isActive = 0 then 'Inactive'\\n        end\\n    ) as StudentActiveInBatch,\\n    min(bs.id) as BatchStudentId,\\n    min(sclb.id) SCLevelBatchId\\nFROM\\n    ltlddb.University u\\n    JOIN ltlddb.School s ON s.university = u.Id\\n    JOIN ltlddb.SchoolCourse sc ON sc.School = s.Id\\n    JOIN ltlddb.SCLevel scl ON scl.SchoolCourse = sc.Id\\n    JOIN ltlddb.level lvl ON scl.Level = lvl.id\\n    JOIN ltlddb.SCLevelBatch sclb ON sclb.SCLevelId = scl.Id\\n    JOIN ltlddb.batchStudent bs ON bs.SCLevelBatch = sclb.Id\\n    JOIN ltlddb.Student st ON st.Id = bs.Student\\n    JOIN bridgedb.IE ie ON ie.Id = st.IE\\n    JOIN bridgedb.Person p ON p.Id = ie.Person\\n    JOIN bridgedb.PersonProfile pp ON p.PersonProfileId = pp.Id\\n    join programtld.worknode on worknode.entityId = s.id\\n    and worknode.entityType = 'ENTITY_TYPE.SCHOOL'\\n    left join programtld.worknodecommunity on worknodecommunity.worknode = worknode.id\\n    left join bridgedb.community com on com.id = worknodecommunity.community\\n    and com.CommunityMinorType = 'COMMUNITY_MINOR_TYPE.LEARNING_CIRCLE'\\n    left join bridgedb.communitymember cm on com.id = cm.CommunityId\\n    and cm.isActive = 1\\n    left join bridgedb.actor cm_ac on cm.ActorId = cm_ac.id\\n    and cm_ac.ActorId = ie.id\\n    LEFT JOIN ltlddb.schoolmltnamem ON schoolmltnamem.school = s.id\\n    LEFT JOIN ltlddb.ltldmltext AS schoolmltext ON schoolmltext.id = schoolmltnamem.mltext\\n    LEFT JOIN ltlddb.universitymltnamem un ON u.id = un.University\\n    LEFT JOIN ltlddb.ltldmltext untxt ON un.MLText = untxt.id\\n    LEFT JOIN ltlddb.coursemltnamem cn on sc.course = cn.course\\n    LEFT JOIN ltlddb.ltldmltext cntxt on cn.MLText = cntxt.id\\n    LEFT JOIN ltlddb.levelmltnamem ln on lvl.id = ln.Level\\n    LEFT JOIN ltlddb.ltldmltext lntxt on ln.MLText = lntxt.id\\n    left join ltlddb.sclevelbatchmltnamem sclName on sclName.LTLDSCLevelBatch = sclb.id\\n    left join ltlddb.ltldmltext batchname on batchname.id = sclName.MLText\\nWHERE\\n    u.tenant = <::TENANT_ID::>\\n    and scl.id in (<::SCLEVEL_ID::>)\\n    AND IF(\\n        <::SCLBATCH_ID::> = - 1,\\n        1 = 1,\\n        sclb.id = <::SCLBATCH_ID::>\\n    )\\ngroup by\\n    st.id,\\n    bs.id;\"}","createdUser":5,"Id":1074},
{"createdChannel":null,"createdVenture":null,"isActive":true,"appUpdatedDateTime":null,"inActiveReason":null,"createdTenant":null,"Encoding":"UTF-8","Title":"[{\"languageCode\":\"ENG\",\"text\":\"Student_Table\"}]","createdIE":null,"updatedVenture":0,"appCreatedDateTime":null,"createdDateTime":"2020-09-30T18:38:24","updatedIE":0,"aliasId":null,"updatedDateTime":"2023-05-25T16:11:30","ContentType":"text/plain","updatedChannel":null,"inActiveDateTime":null,"updatedUser":1,"Description":"[{\"languageCode\":\"ENG\",\"text\":\"Student_Table\"}]","ResultProcessor":null,"Tenant":1,"DataSource":1,"Version":1,"notes":"Created using SO","Name":"Student_Table","updatedTenant":1,"iconpic":null,"DataSetType":"DATASET_TYPE.SQL","Configuration":"{\"queryText\":\"SELECT\\n    replace(\\n        min(st.studentSysGenCode),\\n        concat(min(s.id), '_', min(sclb.id), '_'),\\n        ''\\n    ) AS 'Student_Role_No',\\n    min(st.id) as 'Student_ID',\\n    min(p.id) as 'Person_ID',\\n    min(\\n        CONCAT(pp.firstName, ' ', ifnull(pp.lastname, ''))\\n    ) AS 'Student_Name',\\n    min(\\n        CASE\\n            WHEN Gender = 'GENDER.MALE' THEN 'Male'\\n            WHEN Gender = 'GENDER.FEMALE' THEN 'Female'\\n        END\\n    ) AS 'Gender',\\n    min(untxt.text) AS 'School_Group',\\n    min(schoolmltext.text) AS 'School',\\n    min(lntxt.text) as 'Class',\\n    min(batchname.text) as 'Classroom',\\n    min(\\n        case\\n            when st.isactive = 1 then 'Active'\\n            when st.isactive = 0 then 'Inactive'\\n        end\\n    ) as 'Active_Status',\\n    min(\\n        case\\n            when bs.isActive = 1 then 'Active'\\n            when bs.isActive = 0 then 'Inactive'\\n        end\\n    ) as StudentActiveInBatch,\\n    min(bs.id) as BatchStudentId,\\n    count(distinct cm_ac.id) as 'Community_Count',\\n    min(sclb.AcademicYear) as 'Batch',\\n    min(cntxt.text) as 'Course',\\n    min(sclb.id) SCLevelBatchId\\nFROM\\n    ltlddb.University u\\n    JOIN ltlddb.School s ON s.university = u.Id\\n    JOIN ltlddb.SchoolCourse sc ON sc.School = s.Id\\n    JOIN ltlddb.SCLevel scl ON scl.SchoolCourse = sc.Id\\n    JOIN ltlddb.level lvl ON scl.Level = lvl.id\\n    JOIN ltlddb.SCLevelBatch sclb ON sclb.SCLevelId = scl.Id\\n    JOIN ltlddb.batchStudent bs ON bs.SCLevelBatch = sclb.Id\\n    JOIN ltlddb.Student st ON st.Id = bs.Student\\n    JOIN bridgedb.IE ie ON ie.Id = st.IE\\n    JOIN bridgedb.Person p ON p.Id = ie.Person\\n    JOIN bridgedb.PersonProfile pp ON p.PersonProfileId = pp.Id\\n    join programtld.worknode on worknode.entityId = s.id\\n    and worknode.entityType = 'ENTITY_TYPE.SCHOOL'\\n    left join programtld.worknodecommunity on worknodecommunity.worknode = worknode.id\\n    left join bridgedb.community com on com.id = worknodecommunity.community\\n    and com.CommunityMinorType = 'COMMUNITY_MINOR_TYPE.LEARNING_CIRCLE'\\n    left join bridgedb.communitymember cm on com.id = cm.CommunityId\\n    and cm.isActive = 1\\n    left join bridgedb.actor cm_ac on cm.ActorId = cm_ac.id\\n    and cm_ac.ActorId = ie.id\\n    LEFT JOIN ltlddb.schoolmltnamem ON schoolmltnamem.school = s.id\\n    LEFT JOIN ltlddb.ltldmltext AS schoolmltext ON schoolmltext.id = schoolmltnamem.mltext\\n    LEFT JOIN ltlddb.universitymltnamem un ON u.id = un.University\\n    LEFT JOIN ltlddb.ltldmltext untxt ON un.MLText = untxt.id\\n    LEFT JOIN ltlddb.coursemltnamem cn on sc.course = cn.course\\n    LEFT JOIN ltlddb.ltldmltext cntxt on cn.MLText = cntxt.id\\n    LEFT JOIN ltlddb.levelmltnamem ln on lvl.id = ln.Level\\n    LEFT JOIN ltlddb.ltldmltext lntxt on ln.MLText = lntxt.id\\n    left join ltlddb.sclevelbatchmltnamem sclName on sclName.LTLDSCLevelBatch = sclb.id\\n    left join ltlddb.ltldmltext batchname on batchname.id = sclName.MLText\\nWHERE\\n    u.tenant = <::TENANT_ID::>\\n    and s.id in (<::SCHOOL_ID::>)\\n    and scl.tenant = <::TENANT_ID::>\\n    and sclb.AcademicYear in(<::ACADEMIC_YEAR::>)\\ngroup by\\n    st.id,\\n    bs.id;\"}","createdUser":5,"Id":1077}
]