120 lines
4.6 KiB
Java
120 lines
4.6 KiB
Java
package com.iconplus.smartproc.repository;
|
|
|
|
import com.iconplus.smartproc.model.entity.Users;
|
|
import com.iconplus.smartproc.model.projection.UserRoleView;
|
|
import org.springframework.data.domain.Page;
|
|
import org.springframework.data.domain.Pageable;
|
|
import org.springframework.data.jpa.repository.JpaRepository;
|
|
import org.springframework.data.jpa.repository.Query;
|
|
import org.springframework.stereotype.Repository;
|
|
|
|
import java.util.List;
|
|
import java.util.Optional;
|
|
|
|
@Repository
|
|
public interface UsersRepository extends JpaRepository<Users, Long> {
|
|
|
|
@Query("SELECT u.id as id, " +
|
|
"u.nama as nama, " +
|
|
"u.username as username, " +
|
|
"u.jabatanId as jabatanId, " +
|
|
"j.jabatan as jabatan, " +
|
|
"u.roleId as roleId, " +
|
|
"r.role as role, " +
|
|
"u.bidangId as bidangId, " +
|
|
"b.bidang as bidang, " +
|
|
"u.instansiId as instansiId, " +
|
|
"i.instansi as instansi, " +
|
|
"u.email as email, " +
|
|
"u.telepon as telepon, " +
|
|
"u.isActive as isActive, " +
|
|
"u.isDelete as isDelete " +
|
|
"FROM Users u " +
|
|
"JOIN Roles r ON r.id = u.roleId " +
|
|
"JOIN Bidang b ON b.id = u.bidangId " +
|
|
"JOIN Instansi i ON i.id = u.instansiId " +
|
|
"JOIN Jabatan j ON j.id = u.jabatanId " +
|
|
"Where u.isDelete = false " +
|
|
"AND (:search='' " +
|
|
"or (UPPER(u.nama) like :search " +
|
|
" OR UPPER(j.jabatan) like :search " +
|
|
" OR UPPER(r.role) like :search " +
|
|
" OR UPPER(b.bidang) like :search " +
|
|
" OR UPPER(i.instansi) like :search " +
|
|
" OR UPPER(u.email) like :search) " +
|
|
") " +
|
|
"ORDER by u.id")
|
|
Page<UserRoleView> getAllUsersAndDeletedFase(String search, Pageable pageable);
|
|
|
|
Optional<Users> findByEmailAndIsDeleteFalse(String email);
|
|
|
|
@Query("SELECT u.id as id, " +
|
|
"u.nama as nama, " +
|
|
"u.username as username, " +
|
|
"u.password as password, " +
|
|
"u.jabatanId as jabatanId, " +
|
|
"j.jabatan as jabatan, " +
|
|
"u.roleId as roleId, " +
|
|
"r.role as role, " +
|
|
"u.bidangId as bidangId, " +
|
|
"b.bidang as bidang, " +
|
|
"u.instansiId as instansiId, " +
|
|
"i.instansi as instansi, " +
|
|
"u.email as email, " +
|
|
"u.telepon as telepon, " +
|
|
"u.isActive as isActive, " +
|
|
"u.isDelete as isDelete " +
|
|
"FROM Users u " +
|
|
"JOIN Roles r ON r.id = u.roleId " +
|
|
"JOIN Bidang b ON b.id = u.bidangId " +
|
|
"JOIN Instansi i ON i.id = u.instansiId " +
|
|
"JOIN Jabatan j ON j.id = u.jabatanId " +
|
|
"Where u.isDelete = false " +
|
|
"AND (u.username = :input OR u.email = :input)")
|
|
Optional<UserRoleView> getByUsernameOrEmail(String input);
|
|
|
|
Optional<Users> findByIdAndIsDeleteFalse(Long id);
|
|
|
|
@Query("SELECT u.id as id, " +
|
|
"u.nama as nama, " +
|
|
"u.username as username, " +
|
|
"u.jabatanId as jabatanId, " +
|
|
"j.jabatan as jabatan, " +
|
|
"u.roleId as roleId, " +
|
|
"r.role as role, " +
|
|
"u.bidangId as bidangId, " +
|
|
"b.bidang as bidang, " +
|
|
"u.instansiId as instansiId, " +
|
|
"i.instansi as instansi, " +
|
|
"u.email as email, " +
|
|
"u.telepon as telepon, " +
|
|
"u.isActive as isActive, " +
|
|
"u.isDelete as isDelete " +
|
|
"FROM Users u " +
|
|
"JOIN Roles r ON r.id = u.roleId " +
|
|
"JOIN Bidang b ON b.id = u.bidangId " +
|
|
"JOIN Instansi i ON i.id = u.instansiId " +
|
|
"JOIN Jabatan j ON j.id = u.jabatanId " +
|
|
"Where u.isDelete = false " +
|
|
"AND u.id = :id")
|
|
Optional<UserRoleView> getUserByIdAndDeletedFase(Long id);
|
|
|
|
@Query(value = "SELECT * FROM users " +
|
|
"WHERE remember_id = :rememberId " +
|
|
"AND is_delete = 0 " +
|
|
"AND expired_time >= CURRENT_TIMESTAMP", nativeQuery = true)
|
|
Optional<Users> getUsersByEmailAndExpiredTime(String rememberId);
|
|
|
|
@Query(value = "SELECT u.id as id, " +
|
|
"u.nama as nama, " +
|
|
"u.jabatanId as jabatanId, " +
|
|
"j.jabatan as jabatan " +
|
|
"FROM Users u " +
|
|
"JOIN Jabatan j ON j.id = u.jabatanId " +
|
|
"WHERE u.isDelete = false " +
|
|
"AND j.jabatan like :jabatan " +
|
|
"ORDER BY u.id")
|
|
List<UserRoleView> getListUserJabatan(String jabatan);
|
|
|
|
}
|