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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
package it.niedermann.nextcloud.deck.database.dao;
import androidx.lifecycle.LiveData;
import androidx.room.Dao;
import androidx.room.Query;
import androidx.room.RawQuery;
import androidx.room.Transaction;
import androidx.sqlite.db.SupportSQLiteQuery;
import java.util.List;
import it.niedermann.nextcloud.deck.model.Card;
import it.niedermann.nextcloud.deck.model.full.FullCard;
import it.niedermann.nextcloud.deck.model.full.FullCardWithProjects;
@Dao
public interface CardDao extends GenericDao<Card> {
String QUERY_UPCOMING_CARDS = "SELECT c.* FROM card c " +
"join stack s on s.localId = c.stackId " +
"join board b on b.localId = s.boardId " +
"WHERE b.archived = 0 and c.archived = 0 and b.status <> 3 and s.status <> 3 and c.status <> 3 " +
"and (c.deletedAt is null or c.deletedAt = 0) " +
"and (s.deletedAt is null or s.deletedAt = 0) " +
"and (b.deletedAt is null or b.deletedAt = 0) " +
"and (c.done is null or c.done = 0) " +
// Full Logic: (hasDueDate AND isIn_PRIVATE_Board) OR (isInSharedBoard AND (assignedToMe OR (hasDueDate AND noAssignees)))
"and (" +
"(c.dueDate is not null AND NOT exists(select 1 from AccessControl ac where ac.boardId = b.localId and ac.status <> 3))" + //(hasDueDate AND isInPrivateBoard)
"OR (" +
"exists(select 1 from AccessControl ac where ac.boardId = b.localId and ac.status <> 3) " + //OR (isInSharedBoard AND
"AND (" +
"(c.dueDate is not null AND not exists(select 1 from JoinCardWithUser j where j.cardId = c.localId)) " + // hasDueDate AND noAssignees OR
"OR exists(select 1 from JoinCardWithUser j where j.cardId = c.localId and j.userId in (select u.localId from user u where u.uid in (select a.userName from Account a)))" + //(assignedToMe
")" +
")" +
")" +
"ORDER BY c.dueDate asc";
@Query("SELECT * FROM card WHERE stackId = :localStackId order by `order`, createdAt asc")
LiveData<List<Card>> getCardsForStack(final long localStackId);
@Query("SELECT * FROM card WHERE accountId = :accountId and id = :remoteId")
LiveData<Card> getCardByRemoteId(final long accountId, final long remoteId);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId and id = :remoteId")
FullCard getFullCardByRemoteIdDirectly(final long accountId, final long remoteId);
@Query("SELECT * FROM card WHERE accountId = :accountId and localId = :localId")
Card getCardByLocalIdDirectly(final long accountId, final long localId);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId and localId = :localId")
FullCard getFullCardByLocalIdDirectly(final long accountId, final long localId);
@Transaction
// v not deleted!
@Query("SELECT * FROM card WHERE accountId = :accountId AND archived = 0 AND stackId = :localStackId and status<>3 order by `order`, createdAt asc")
LiveData<List<FullCard>> getFullCardsForStack(final long accountId, final long localStackId);
@Transaction
@RawQuery(observedEntities = Card.class)
LiveData<List<FullCard>> getFilteredFullCardsForStack(SupportSQLiteQuery query);
@Transaction
@RawQuery(observedEntities = Card.class)
List<FullCard> getFilteredFullCardsForStackDirectly(SupportSQLiteQuery query);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId AND stackId = :localStackId order by `order`, createdAt asc")
List<FullCard> getFullCardsForStackDirectly(final long accountId, final long localStackId);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId and localId = :localCardId")
LiveData<FullCard> getFullCardByLocalId(final long accountId, final long localCardId);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId and localId = :localCardId")
LiveData<FullCardWithProjects> getFullCardWithProjectsByLocalId(final long accountId, final long localCardId);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId and id = :remoteId")
LiveData<FullCard> getFullCardByRemoteId(final long accountId, final long remoteId);
@Query("SELECT * FROM card WHERE accountId = :accountId and id = :remoteId")
Card getCardByRemoteIdDirectly(long accountId, long remoteId);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId and (status<>1 or id is null or lastModified <> lastModifiedLocal)")
List<FullCard> getLocallyChangedCardsDirectly(long accountId);
@Transaction
@Query("SELECT * FROM card WHERE accountId = :accountId and stackId = :localStackId and (status<>1 or id is null or lastModified <> lastModifiedLocal)")
List<FullCard> getLocallyChangedCardsByLocalStackIdDirectly(long accountId, long localStackId);
@Query("SELECT * FROM card c WHERE accountId = :accountId and exists ( select 1 from DeckComment dc where dc.objectId = c.localId and dc.status<>1)")
List<Card> getCardsWithLocallyChangedCommentsDirectly(Long accountId);
@Query("SELECT * FROM card c WHERE stackId = :localStackId and exists ( select 1 from DeckComment dc where dc.objectId = c.localId and dc.status<>1)")
List<Card> getCardsWithLocallyChangedCommentsForStackDirectly(Long localStackId);
@Query("SELECT count(*) FROM card c WHERE accountId = :accountId and stackId = :localStackId and status <> 3")
int countCardsInStackDirectly(long accountId, long localStackId);
@Query("SELECT coalesce(MAX(`order`), -1) FROM card c WHERE stackId = :localStackId and status <> 3")
Integer getHighestOrderInStack(Long localStackId);
@Query("SELECT c.stackId FROM card c WHERE localId = :localCardId")
Long getLocalStackIdByLocalCardId(Long localCardId);
@Transaction
@Query("SELECT * FROM card c WHERE " +
"exists(select 1 from Stack s join Board b on s.boardId = b.localId where s.localId = c.stackId " +
"and b.archived = 0 " +
"and not exists(select 1 from AccessControl ac where ac.boardId = b.localId and status <> 3)) " +
"and dueDate is not null " +
"and (coalesce(:accountIds, null) is null or accountId in (:accountIds)) " +
"and status <> 3 " +
"and archived = 0")
List<FullCard> getFullCardsForNonSharedBoardsWithDueDateForUpcomingCardsWidgetDirectly(List<Long> accountIds);
@Transaction
@Query(QUERY_UPCOMING_CARDS)
LiveData<List<FullCard>> getUpcomingCards();
@Transaction
@Query(QUERY_UPCOMING_CARDS)
List<FullCard> getUpcomingCardsDirectly();
@Transaction
@Query("SELECT c.* FROM card c " +
"inner join Stack s on c.stackId = s.localId " +
"WHERE s.boardId = :localBoardId " +
"and (c.title like :term or c.description like :term) " +
"and c.accountId = :accountId " +
"and s.accountId = :accountId " +
"and c.status <> 3 " +
"and s.status <> 3 " +
"and c.archived = 0 " +
"order by s.`order`, c.`order`")
LiveData<List<FullCard>> searchCard(long accountId, long localBoardId, String term);
}
|
