Common-API/src/main/java/com/iemr/common/repository/users/FacilityLoginRepo.java at 7333580476b01433a591ec0ea2d3b74b429b3e5b · PSMRI/Common-API · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
package com.iemr.common.repository.users;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.iemr.common.data.users.AshaSupervisorMapping;

@Repository
public interface FacilityLoginRepo extends CrudRepository<AshaSupervisorMapping, Long> {

	// Get user's facility IDs from m_UserServiceRoleMapping (for ANY user)
	@Query(value = "SELECT DISTINCT usrm.FacilityID "
			+ "FROM m_UserServiceRoleMapping usrm "
			+ "WHERE usrm.UserID = :userID AND usrm.Deleted = false "
			+ "AND usrm.FacilityID IS NOT NULL", nativeQuery = true)
	List<Integer> getUserFacilityIDs(@Param("userID") Integer userID);

	// Get facility details with geo names and facilityType
	@Query(value = "SELECT DISTINCT f.FacilityID, f.FacilityName, "
			+ "f.StateID, COALESCE(s.StateName,'') AS stateName, "
			+ "f.DistrictID, COALESCE(d.DistrictName,'') AS districtName, "
			+ "f.BlockID, COALESCE(b.BlockName,'') AS blockName, "
			+ "COALESCE(f.RuralUrban,'') AS ruralUrban, "
			+ "COALESCE(ft.FacilityTypeName,'') AS facilityTypeName "
			+ "FROM m_facility f "
			+ "LEFT JOIN m_state s ON s.StateID = f.StateID "
			+ "LEFT JOIN m_district d ON d.DistrictID = f.DistrictID "
			+ "LEFT JOIN m_districtblock b ON b.BlockID = f.BlockID "
			+ "LEFT JOIN m_facilitytype ft ON ft.FacilityTypeID = f.FacilityTypeID "
			+ "WHERE f.FacilityID IN :facilityIDs AND f.Deleted = false", nativeQuery = true)
	List<Object[]> getFacilityDetails(@Param("facilityIDs") List<Integer> facilityIDs);

	// ASHA login: get supervisor details
	@Query(value = "SELECT asm.supervisorUserID, u.FirstName, u.LastName, u.ContactNo, "
			+ "COALESCE(u.EmployeeID,'') AS employeeID "
			+ "FROM asha_supervisor_mapping asm "
			+ "JOIN m_User u ON u.UserID = asm.supervisorUserID "
			+ "WHERE asm.ashaUserID = :ashaUserID AND asm.deleted = false "
			+ "AND u.Deleted = false LIMIT 1", nativeQuery = true)
	List<Object[]> getSupervisorForAsha(@Param("ashaUserID") Integer ashaUserID);

	@Query(value = "SELECT GenderName FROM m_gender WHERE GenderID = :genderID", nativeQuery = true)
	String getGenderName(@Param("genderID") Integer genderID);

	// Villages mapped to facilities
	@Query(value = "SELECT fvm.FacilityID, fvm.DistrictBranchID, dbm.VillageName "
			+ "FROM facility_village_mapping fvm "
			+ "JOIN m_DistrictBranchMapping dbm ON dbm.DistrictBranchID = fvm.DistrictBranchID "
			+ "WHERE fvm.FacilityID IN :facilityIDs AND fvm.Deleted = false", nativeQuery = true)
	List<Object[]> getVillagesForFacilities(@Param("facilityIDs") List<Integer> facilityIDs);

	// ASHA login: get peers at same facility (ANM, CHO, etc.)
	@Query(value = "SELECT DISTINCT usrm.UserID, u.FirstName, u.LastName, r.RoleName, "
			+ "COALESCE(u.EmployeeID,'') AS employeeID "
			+ "FROM m_UserServiceRoleMapping usrm "
			+ "JOIN m_User u ON u.UserID = usrm.UserID "
			+ "JOIN m_Role r ON r.RoleID = usrm.RoleID "
			+ "WHERE usrm.FacilityID IN :facilityIDs "
			+ "AND usrm.UserID != :currentUserID "
			+ "AND usrm.Deleted = false AND u.Deleted = false", nativeQuery = true)
	List<Object[]> getPeersAtFacility(@Param("facilityIDs") List<Integer> facilityIDs,
			@Param("currentUserID") Integer currentUserID);
}