#52: provide proper data

3 files changed, 28 insertions, 11 deletions

diff --git a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DataBaseAdapter.java b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DataBaseAdapter.java
index e478583ef..b7f328c40 100644
--- a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DataBaseAdapter.java
+++ b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/DataBaseAdapter.java
@@ -397,8 +397,8 @@ public class DataBaseAdapter {
         return db.getLabelDao().searchLabelByTitle(accountId, boardId,"%"+searchTerm.trim()+"%");
     }
 
-    public LiveData<List<User>> findProposalsForUsersToAssign(final long accountId, long notAssignedToLocalCardId, final int topX){
-        return db.getUserDao().findProposalsForUsersToAssign(accountId, notAssignedToLocalCardId, topX);
+    public LiveData<List<User>> findProposalsForUsersToAssign(final long accountId, long boardId, long notAssignedToLocalCardId, final int topX){
+        return db.getUserDao().findProposalsForUsersToAssign(accountId, boardId, notAssignedToLocalCardId, topX);
     }
 
     public LiveData<List<Label>> findProposalsForLabelsToAssign(final long accountId, final long boardId, long notAssignedToLocalCardId, final int topX){
diff --git a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/LabelDao.java b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/LabelDao.java
index c867274be..df2136433 100644
--- a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/LabelDao.java
+++ b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/LabelDao.java
@@ -36,10 +36,10 @@ public interface LabelDao extends GenericDao<Label> {
     List<Label> getLocallyChangedLabelsDirectly(long accountId);
 
     @Query("SELECT l.* " +
-            "FROM joincardwithlabel j LEFT JOIN label l ON j.labelId = l.localId " +
+            "FROM label l LEFT JOIN joincardwithlabel j ON j.labelId = l.localId " +
             "WHERE l.accountId = :accountId AND l.boardId = :boardId " +
             "AND NOT EXISTS (select 1 from joincardwithlabel jl where jl.labelId = l.localId and jl.cardId = :notAssignedToLocalCardId) " +
-            "GROUP BY j.labelId ORDER BY count(*) DESC " +
+            "GROUP BY l.localId ORDER BY count(*) DESC " +
             "LIMIT :topX")
     LiveData<List<Label>> findProposalsForLabelsToAssign(long accountId, long boardId, long notAssignedToLocalCardId, int topX);
 }
\ No newline at end of file
diff --git a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/UserDao.java b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/UserDao.java
index d324519f3..00635f28d 100644
--- a/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/UserDao.java
+++ b/app/src/main/java/it/niedermann/nextcloud/deck/persistence/sync/adapters/db/dao/UserDao.java
@@ -32,11 +32,28 @@ public interface UserDao extends GenericDao<User> {
     @Query("SELECT * FROM user WHERE localId = :localUserId")
     User getUserByLocalIdDirectly(long localUserId);
 
-    @Query("SELECT u.* " +
-            "FROM joincardwithuser j LEFT JOIN user u ON j.userId = u.localId " +
-            "WHERE u.accountId = :accountId " +
-            "AND NOT EXISTS (select 1 from joincardwithuser ju where ju.userId = u.localId and ju.cardId = :notAssignedToLocalCardId) " +
-            "GROUP BY j.userId ORDER BY count(*) DESC " +
-            "LIMIT :topX")
-    LiveData<List<User>> findProposalsForUsersToAssign(long accountId, long notAssignedToLocalCardId, int topX);
+    @Query("    SELECT u.* FROM user u" +
+            "    WHERE u.accountId = :accountId" +
+            "    AND NOT EXISTS (" +
+            "            select 1 from joincardwithuser ju" +
+            "            where ju.userId = u.localId" +
+            "            and ju.cardId = :notAssignedToLocalCardId" +
+            "    )" +
+            "    AND" +
+            "            (" +
+            "                    EXISTS (" +
+            "                    select 1 from accesscontrol" +
+            "                    where userId = u.localId and boardId = :boardId" +
+            "            )" +
+            "    OR" +
+            "    EXISTS (" +
+            "            select 1 from board where localId = :boardId AND ownerId = u.localId" +
+            "    )" +
+            ")" +
+            "    ORDER BY (" +
+            "            select count(*) from joincardwithuser j" +
+            "    where userId = u.localId and cardId in (select c.localId from card c inner join stack s on s.localId = c.stackId where s.boardId = :boardId)" +
+            ") DESC" +
+            "    LIMIT :topX")
+    LiveData<List<User>> findProposalsForUsersToAssign(long accountId, long boardId, long notAssignedToLocalCardId, int topX);
 }
\ No newline at end of file