Revision 53200
Added by Konstantina Galouni over 5 years ago
QueryGenerator.java | ||
---|---|---|
732 | 732 |
clauseParams.add(dateTo); |
733 | 733 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause, clauseParams,keyword,types, params); |
734 | 734 |
} |
735 |
|
|
736 |
public String generateFetchNumberOfClaimsByDateAndOpenaireId(String dateFrom, String dateTo, String openaireId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc,List<String> types, ArrayList<Object> params) { |
|
737 |
String clause= " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp and source.openaire_id =? "; |
|
738 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
|
739 |
clauseParams.add(dateFrom); |
|
740 |
clauseParams.add(dateTo); |
|
741 |
clauseParams.add(openaireId); |
|
742 |
return generateSelectCountClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause, clauseParams,keyword,types, params); |
|
743 |
} |
|
744 |
|
|
735 | 745 |
public String generateFetchClaimsByProject(String projectId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) { |
736 | 746 |
String clause = "source.openaire_id =?"; |
737 | 747 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
... | ... | |
922 | 932 |
|
923 | 933 |
return query; |
924 | 934 |
} |
935 |
private String generateSelectCountClaimQueryAsUnionOfAllRelations(Integer limit,Integer offset,String orderBy, boolean desc,String specificWhereClause,ArrayList<Object> whereParams, String keyword,List<String> types, ArrayList<Object> params) { |
|
936 |
//TODO eliminate unions based on the to |
|
937 |
// ClaimUtils.PUBLICATION or ClaimUtils.DATASET it |
|
938 |
String orderByClause= addOrderByClause(orderBy,desc); |
|
939 |
String pagingClause= addPagingClause(limit,offset); |
|
940 |
String query = "select count(id) from ("+ |
|
941 |
|
|
942 |
" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,whereParams,keyword, params)+" ) \nunion "+ |
|
943 |
" ( " +generateSelectclaimQuery(ClaimUtils.DATASET,ClaimUtils.DATASET,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+ |
|
944 |
" ( " +generateSelectclaimQuery(ClaimUtils.SOFTWARE,ClaimUtils.SOFTWARE,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+ |
|
945 |
" ( " +generateSelectclaimQuery(ClaimUtils.OTHER,ClaimUtils.OTHER,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+ |
|
946 |
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+ |
|
947 |
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause, whereParams, keyword, params)+" )" + |
|
948 |
|
|
949 |
") as claim "; |
|
950 |
String filterByType = addFilterByType(types, params); |
|
951 |
query += ((filterByType==null)?"":" where "+filterByType+" ")+ |
|
952 |
((orderByClause==null)?"":" "+orderByClause+" ")+ |
|
953 |
pagingClause; |
|
954 |
|
|
955 |
return query; |
|
956 |
} |
|
957 |
|
|
925 | 958 |
private String getTypeForTable(String type){ |
926 | 959 |
if(type == null){ |
927 | 960 |
return null; |
... | ... | |
1067 | 1100 |
return null; |
1068 | 1101 |
} |
1069 | 1102 |
|
1103 |
public String generateSelectFirstContextByCommunityIdQuery(String communityId, ArrayList<Object> params) { |
|
1104 |
params.add(communityId+"%"); |
|
1105 |
return " Select " + getContextFields("context") + " from context where openaire_id like '?' LIMIT 1"; |
|
1106 |
} |
|
1107 |
|
|
1070 | 1108 |
public String generateSelectProjectByIdQuery(String projectId, ArrayList<Object> params) { |
1071 | 1109 |
params.add(projectId); |
1072 | 1110 |
return " Select " + getProjectFields("project") + " from project where openaire_id = ?"; |
... | ... | |
1074 | 1112 |
|
1075 | 1113 |
public String generateSelectContactEmailsByProjectIdQuery(String projectId, ArrayList<Object> params) { |
1076 | 1114 |
params.add(projectId); |
1077 |
return " Select contact_person from project where openaire_id = ?"; |
|
1115 |
//return " SELECT contact_person FROM project WHERE openaire_id = ? ;"; |
|
1116 |
return "SELECT DISTINCT unnest(contact_person) as contact_person FROM project WHERE openaire_id = ?;"; |
|
1078 | 1117 |
} |
1079 | 1118 |
|
1119 |
public String generateSelectProjectIdsAndNamesByProjectManagerMail(String userMail, ArrayList<Object> params) { |
|
1120 |
params.add(userMail); |
|
1121 |
return "SELECT openaire_id, name FROM project WHERE ? = ANY (contact_person);"; |
|
1122 |
} |
|
1123 |
|
|
1080 | 1124 |
public String generateUpdateTokenByProjectId(String projectdId, String newToken, ArrayList<Object> params) { |
1081 | 1125 |
params.add(newToken); |
1082 | 1126 |
params.add(projectdId); |
... | ... | |
1118 | 1162 |
return query; |
1119 | 1163 |
} |
1120 | 1164 |
|
1165 |
public String generateInsertNotificationQuery(Date date, String openaire_id, String userMail, int frequency, boolean notify , ArrayList<Object> params) { |
|
1166 |
|
|
1167 |
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); |
|
1168 |
String dateStr=null; |
|
1169 |
dateStr=(format.format(date)); |
|
1170 |
String fields="last_interaction_date, user_email, frequency, notify, openaire_id"; |
|
1171 |
|
|
1172 |
String values="?::timestamp,?,?,?,?"; |
|
1173 |
params.add(dateStr); |
|
1174 |
params.add(userMail); |
|
1175 |
params.add(frequency); |
|
1176 |
params.add(notify); |
|
1177 |
params.add(openaire_id); |
|
1178 |
|
|
1179 |
return " INSERT INTO notification( "+fields+")\n" + |
|
1180 |
" VALUES ( "+values+")\n" + |
|
1181 |
" RETURNING openaire_id, user_email\n"; |
|
1182 |
} |
|
1183 |
|
|
1184 |
public String generateUpdateNotificationPreferences(String openaire_id, String userMail, int frequency, boolean notify, ArrayList<Object> params) { |
|
1185 |
params.add(frequency); |
|
1186 |
params.add(notify); |
|
1187 |
params.add(userMail); |
|
1188 |
params.add(openaire_id); |
|
1189 |
String query = "UPDATE notification " |
|
1190 |
+ "SET frequency = ?, notify = ? " |
|
1191 |
+ "WHERE user_email = ? AND openaire_id = ?";// RETURNING curation_date, curated_by, approved"; |
|
1192 |
|
|
1193 |
logger.debug("Query to execute: "+query); |
|
1194 |
|
|
1195 |
return query; |
|
1196 |
} |
|
1197 |
|
|
1198 |
public String generateUpdateNotificationLastInteractionDate(String openaire_id, String userMail, Date date, ArrayList<Object> params) { |
|
1199 |
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); |
|
1200 |
String dateStr=null; |
|
1201 |
dateStr=(format.format(date)); |
|
1202 |
|
|
1203 |
params.add(dateStr); |
|
1204 |
params.add(userMail); |
|
1205 |
params.add(openaire_id); |
|
1206 |
String query = "UPDATE notification " |
|
1207 |
+ "SET last_interaction_date = ?::timestamp " |
|
1208 |
+ "WHERE user_email = ? AND openaire_id = ?";// RETURNING curation_date, curated_by, approved"; |
|
1209 |
|
|
1210 |
logger.debug("Query to execute: "+query); |
|
1211 |
|
|
1212 |
return query; |
|
1213 |
} |
|
1214 |
|
|
1215 |
public String generateSelectNotificationQuery(String openaire_id, String userMail, ArrayList<Object> params) { |
|
1216 |
params.add(userMail); |
|
1217 |
params.add(openaire_id); |
|
1218 |
String query = "SELECT * FROM notification WHERE user_email = ? AND openaire_id = ?"; |
|
1219 |
|
|
1220 |
logger.debug("Query to execute: "+query); |
|
1221 |
|
|
1222 |
return query; |
|
1223 |
} |
|
1224 |
|
|
1225 |
public String generateSelectTrueNotificationsQuery() { |
|
1226 |
String query = "SELECT * FROM notification WHERE notify=true"; |
|
1227 |
|
|
1228 |
logger.debug("Query to execute: "+query); |
|
1229 |
|
|
1230 |
return query; |
|
1231 |
} |
|
1232 |
|
|
1121 | 1233 |
public String getMigrationTable() { |
1122 | 1234 |
return migrationTable; |
1123 | 1235 |
} |
Also available in: Unified diff
1. Add entity 'Notification': pairs of mail-openaireId, with the preferences for notifications of this user for this id.
2. Add FetchNotificationHandler and NotificationHandler for 'Notification' entity.
3. springContext-claimsDemo.xml: Add beans for FetchNotificationHandler, NotificationHandler, defaultFrequencyInHours.
4. QueryGenerator.java: Add methods:
generateFetchNumberOfClaimsByDateAndOpenaireId, generateSelectCountClaimQueryAsUnionOfAllRelations,
generateSelectFirstContextByCommunityIdQuery, generateSelectProjectIdsAndNamesByProjectManagerMail,
generateInsertNotificationQuery, generateUpdateNotificationPreferences, generateUpdateNotificationLastInteractionDate,
generateSelectNotificationQuery, generateSelectTrueNotificationsQuery
Change method: generateSelectContactEmailsByProjectIdQuery (unnest the sql array).