ClassRoomGetUsersByRoles

select u.id as id, concat( trim(u.firstName), ' ', ifnull(trim(u.lastName), '') ) as UserName, u.firstName, u.Login as Login, COALESCE(min(c.ContactValue), min(pcc.ContactValue)) as Contact, COALESCE(min(ce.ContactValue), min(pce.ContactValue)) as Email, min(role.Code) as Code, min(role.RoleName) as RoleName, group_concat(distinct urm.RoleCode) as Roles, case when u.isActive then 'active' else 'inActive' end as activeStatus, urm.createdDateTime UserRoleCreatedDate, SUBSTRING(pp.gender, 8) Gender, pp.DateOfBirth DOB, u.iconpic UserIconPic, person.iconpic PersonIconPic from bridgedb.user u left join bridgedb.person on person.id = u.person left join bridgedb.personprofile pp on person.PersonProfileId = pp.id left join bridgedb.usercontact uc on ( uc.UserId = u.id and uc.isActive = 1 and uc.id is not null ) left join bridgedb.personcontact pc on ( pc.personid = person.id and pc.isActive = 1 and pc.id is not null ) left join bridgedb.contact pcc on pcc.id = pc.ContactId and pcc.ContactType = 'CONTACT_TYPE.MOBILE' left join bridgedb.contact pce on pce.id = pc.ContactId and pce.ContactType = 'CONTACT_TYPE.MAIL' left join bridgedb.contact c on c.id = uc.ContactId and c.ContactType = 'CONTACT_TYPE.MOBILE' left join bridgedb.contact ce on ce.id = uc.ContactId and ce.ContactType = 'CONTACT_TYPE.MAIL' left join bridgedb.userrolemap urm on urm.UserId = u.id and urm.isActive = 1 left join bridgedb.role on urm.RoleCode = role.Code where u.TenantId = <::CURRENT_TENANT_ID::> AND urm.RoleCode in (<::IN_PARAM_ROLECODE::>) AND u.isActive = 1 AND IF( <::SEARCH_TEXT::> = 'ALL', 1 = 1, ( pce.ContactValue like <::SEARCH_TEXT::> or ce.ContactValue like <::SEARCH_TEXT::> OR u.FirstName like <::SEARCH_TEXT::> OR u.lastName like <::SEARCH_TEXT::> OR concat( ifnull(u.FirstName, ''), ' ', ifnull(u.lastName, '') ) like <::SEARCH_TEXT::> OR u.FirstName like concat( '%', SUBSTRING_INDEX(<::SEARCH_TEXT::>, ' ', 1), '%' ) OR u.lastName like concat( '%', SUBSTRING_INDEX(<::SEARCH_TEXT::>, ' ', -1), '%' ) ) ) group by u.id order by <::SORT_BY::> <::SORT_ORDER::> limit <::LIMIT_OFFSET::>, <::LIMIT_ROW_COUNT::>;