Coverage for middle_layer/propose/application_layer/orm_repositories/proposal.py: 87.91%

1# Copyright 2024 Associated Universities, Inc. 

2# 

3# This file is part of Telescope Time Allocation Tools (TTAT). 

4# 

5# TTAT is free software: you can redistribute it and/or modify 

6# it under the terms of the GNU General Public License as published by 

7# the Free Software Foundation, either version 3 of the License, or 

8# any later version. 

9# 

10# TTAT is distributed in the hope that it will be useful, 

11# but WITHOUT ANY WARRANTY; without even the implied warranty of 

12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 

13# GNU General Public License for more details. 

14# 

15# You should have received a copy of the GNU General Public License 

16# along with TTAT. If not, see <https://www.gnu.org/licenses/>. 

17# 

18from typing import Any, Sequence 

19 

20from sqlalchemy import Row, RowMapping, func, text 

21from sqlalchemy.dialects.postgresql import ts_headline, websearch_to_tsquery 

22from sqlalchemy.orm import Session 

23 

24from closeout.domain_layer.entities.proposals_search_result import ProposalSearchResult 

25from common.application_layer.orm_repositories import add_entity, get_object_by_id 

26from propose.domain_layer.entities.proposal import Author, Proposal 

27from propose.domain_layer.repositories.proposal import ProposalRepository 

28from review.domain_layer.entities.individual_science_review import IndividualScienceReview 

29from review.domain_layer.entities.science_reviewer import ScienceReviewer 

30 

31 

32class ProposalORMRepository(ProposalRepository): 

33 def __init__(self, session: Session): 

34 self.session = session 

35 

36 def by_id(self, proposal_id: int) -> Proposal: 

37 return get_object_by_id(self.session, proposal_id, Proposal, Proposal.proposal_id) 

38 

39 def list_all(self) -> list[Proposal]: 

40 return list(self.session.query(Proposal).filter(Proposal.state != "Hidden").order_by(Proposal.proposal_id)) 

41 

42 def list_filtered( 

43 self, 

44 visibility_user_id: int = None, 

45 state: str = None, 

46 solicitation_id: int = None, 

47 user_id: int = None, 

48 ) -> list[Proposal]: 

49 """ 

50 Repository that accepts filtering parameters 

51 

52 :kwargs: expecting visibility_user_id, state, solicitation_id, and user_id. 

53 :return: list of matching proposals from the DB. 

54 """ 

55 

56 # first, the base query 

57 query = self.session.query(Proposal) 

58 

59 # check if the result should be restricted to the current user: 

60 # if non-tta member, no hidden proposals are returned 

61 if visibility_user_id is not None: 

62 query = query.filter(Proposal.authors.any(Author.user_id == visibility_user_id)) 

63 

64 # check if there is a filter on state, and include it: 

65 if state is not None: 

66 query = query.filter(Proposal.state == state) 

67 

68 # check if there is a filter on solicitation_id: 

69 if solicitation_id is not None: 

70 query = query.filter(Proposal.solicitation_id == solicitation_id) 

71 

72 # check if there is a filter on user_id: 

73 if user_id is not None: 

74 query = query.filter(Proposal.authors.any(Author.user_id == user_id)) 

75 

76 return list(query.order_by(Proposal.proposal_id).all()) 

77 

78 def list_by_user_id_author(self, user_id: int, exclude_hidden: bool = True) -> list[Proposal]: 

79 query = self.session.query(Proposal).join(Author).filter(Author.user_id == user_id) 

80 if exclude_hidden: 

81 query = query.filter(Proposal.state != "Hidden") 

82 return query.all() 

83 

84 def list_by_user_id_science_reviewer(self, user_id: int, exclude_hidden: bool = True) -> list[Proposal]: 

85 query = ( 

86 self.session.query(Proposal) 

87 .join(IndividualScienceReview) 

88 .join(ScienceReviewer) 

89 .filter(ScienceReviewer.user_id == user_id) 

90 ) 

91 if exclude_hidden: 

92 query = query.filter(Proposal.state != "Hidden") 

93 return query.all() 

94 

95 def add(self, proposal: Proposal) -> int: 

96 add_entity(self.session, proposal) 

97 return proposal.proposal_id 

98 

99 def update(self, proposal: Proposal) -> None: 

100 p = self.by_id(proposal.proposal_id) 

101 p.solicitation_id = proposal.solicitation_id 

102 p.state = proposal.state 

103 p.submitted_timestamp = proposal.submitted_timestamp 

104 p.authors = proposal.authors 

105 p.is_discussed = proposal.is_discussed 

106 p.is_triggered = proposal.is_triggered 

107 p.prototype_projects = proposal.prototype_projects 

108 self.session.flush() 

109 

110 def summary_filtered( 

111 self, 

112 solicitation_id: int, 

113 published_for: str | None = None, 

114 facility_id: int | None = None, 

115 is_triggered: bool | None = None, 

116 is_joint: bool | None = None, 

117 is_thesis: bool | None = None, 

118 proposal_class_id: int | None = None, 

119 allocation_version_id: int | None = None, 

120 ) -> Sequence[RowMapping]: 

121 """ 

122 Repository that accepts filtering parameters and produces proposal summaries 

123 

124 :kwargs: expecting solicitation_id, also accepts facility_id, is_triggered, is_joint, is_thesis(ignored), 

125 and proposal_class_id. 

126 :return: list of matching proposal summaries from the DB. 

127 """ 

128 valid_publish_states = {"DISPOSITION", "APPROVAL", "CLOSEOUT"} 

129 if published_for not in valid_publish_states and allocation_version_id is None: 

130 raise ValueError( 

131 f"'{published_for}' is not a valid publish state. Expected one of {valid_publish_states} " 

132 f"or an allocation_version_id" 

133 ) 

134 

135 fields = ( 

136 "proposals.proposal_id as proposal_id, " 

137 "prop_reviews.scientific_merit_metric as scientific_merit_metric, " 

138 "pi_name as pi_name, " 

139 "coi_names as coi_names, " 

140 "proposals.is_triggered as is_triggered, " 

141 "false as is_thesis, " 

142 "copies.title as title, " 

143 "copies.abstract as abstract, " 

144 "classes.proposal_class_name as proposal_class, " 

145 # Proposal Review process tag (e.g., SRP name) 

146 "srp.science_review_panel_name as review_process_tag, " 

147 # External Joint facility + hours (e.g., ALMA 5hrs; HST 2 orbits) 

148 "'' as external_joint_facilities, " # (not currently tracked) 

149 # Internal Joint Facilities (show all that apply despite filtering; e.g., GBT, VLA) 

150 "ar_facs.facilities as internal_joint_facilities, " 

151 # Total Requested Hours of selected Facility 

152 "total_requested_time as total_requested_hours, " 

153 # Scheduling Priorities (unique set from OSDs of selected Facility) 

154 "prop_osd.priorities as priorities, " 

155 # Requested Semesters and VLA Configurations (if applicable) of ADs of selected Facility 

156 "coalesce(prop_osd.facility_configurations, 'No HW Config') as facility_configurations, " 

157 # TODO:Hardware Bands (unique set from ADs of selected Facility; need to talk through what's possible atm) 

158 # "concat(frontends,',',backends) as hardware_bands, " 

159 "'FE here, BE here' as hardware_bands, " 

160 # Recommended Time with Priority A in Hours of selected Facility 

161 "a_time as priority_a, " 

162 # Recommended Time with Priority B in Hours of selected Facility 

163 "b_time as priority_b, " 

164 # Recommended Time with Priority C in Hours of selected Facility 

165 "c_time as priority_c, " 

166 # Recommended Time with Priority N in Hours of selected Facility 

167 "n_time as priority_n, " 

168 # Scheduler Comments 

169 # (All Scheduler Comments for TAC for ADs of selected facility appended together per proposal) 

170 "scheduler_comments as scheduler_comments, " 

171 # Science Comments to PI (external science review comments) 

172 "prop_reviews.external_science_review_comments as external_science_review_comments, " 

173 # Technical Comments to PI (external technical review comments) 

174 "prop_reviews.external_technical_review_comments as external_technical_review_comments, " 

175 # Data Comments to PI (external data management review comments) 

176 "prop_reviews.external_data_management_review_comments as external_data_management_review_comments, " 

177 # Science Internal Comments (internal scientific review comments) 

178 "prop_reviews.internal_science_review_comments as internal_science_Review_comments, " 

179 # Technical Internal Comments (internal technical review comments) 

180 "prop_reviews.internal_technical_review_comments as internal_technical_Review_comments, " 

181 # Data Internal Comments (internal data management review comments) 

182 "prop_reviews.internal_data_management_review_comments as internal_data_management_review_comments, " 

183 # Comments to PI (from Proposal Disposition; could be blank) 

184 "proposal_dispositions.external_allocation_disposition_comments as external_allocation_disposition_comments, " 

185 # Internal Comments (from Proposal Disposition; could be blank) 

186 "proposal_dispositions.internal_allocation_disposition_comments as internal_allocation_disposition_comments " 

187 ) 

188 pi_query = ( 

189 f"select proposal_id, concat(first_name, ' ', last_name) as pi_name " 

190 f"from authors where is_primary = True" 

191 ) 

192 coi_query = ( 

193 f"select proposal_id, string_agg(concat(first_name, ' ', last_name),',') as coi_names " 

194 f"from authors where is_primary = False group by proposal_id" 

195 ) 

196 prop_review_fields = ( 

197 f"proposal_id, scientific_merit_metric, " 

198 f"external_science_review_comments, internal_science_review_comments, " 

199 f"external_technical_review_comments, internal_technical_review_comments, " 

200 f"external_data_management_review_comments, internal_data_management_review_comments" 

201 ) 

202 prop_reviews_query = ( 

203 f"select {prop_review_fields} from ppr_proposal_reviews " 

204 f"union select {prop_review_fields} from osr_proposal_reviews" 

205 ) 

206 ar_facilities_query = ( 

207 "select string_agg(distinct facility_name, ',') facilities, proposal_copy_id " 

208 f"from facilities inner join allocation_requests ar " 

209 f"on facilities.facility_id = ar.facility_id " 

210 f"where facilities.facility_id != {facility_id} " 

211 f"group by ar.proposal_copy_id " 

212 ) 

213 # TODO: this can be simplified with a pivot or crosstab query 

214 osd_a_query = ( 

215 f"select allocation_request_id, allocated_time_per_repeat as priority_a_time " 

216 f"from observation_specification_dispositions osd " 

217 f"where scheduling_priority_name = 'A' " 

218 ) 

219 osd_b_query = ( 

220 f"select allocation_request_id, allocated_time_per_repeat as priority_b_time " 

221 f"from observation_specification_dispositions osd " 

222 f"where scheduling_priority_name = 'B' " 

223 ) 

224 osd_c_query = ( 

225 f"select allocation_request_id, allocated_time_per_repeat as priority_c_time " 

226 f"from observation_specification_dispositions osd " 

227 f"where scheduling_priority_name = 'C' " 

228 ) 

229 osd_n_query = ( 

230 f"select allocation_request_id, allocated_time_per_repeat as priority_n_time " 

231 f"from observation_specification_dispositions osd " 

232 f"where scheduling_priority_name = 'N' " 

233 ) 

234 # get the configuration (as a string) for each OSD: 

235 hw_config_query = ( 

236 "select hw.hardware_configuration_id, " 

237 "string_agg(f.frontend_name, ',') as frontends, " 

238 "string_agg(b.backend_name, ',') as backends " 

239 "from hardware_configurations hw " 

240 "inner join frontend_configurations fe on hw.frontend_configuration_id = fe.frontend_configuration_id " 

241 "inner join frontends f on f.frontend_id = fe.frontend_id " 

242 "inner join backend_configurations be on hw.backend_configuration_id = be.backend_configuration_id " 

243 "inner join backends b on b.backend_id = be.backend_id " 

244 "group by hw.hardware_configuration_id " 

245 ) 

246 configuration_query = ( 

247 f"select osd.observation_specification_disposition_id as osd_id, concat(frontends, backends) as hw_string " 

248 f"from observation_specification_dispositions osd " 

249 f"inner join ({hw_config_query}) hw " 

250 f"on hw.hardware_configuration_id = osd.requested_hardware_configuration_id " 

251 ) 

252 osd_query = ( 

253 f"select sum(osd.allocated_time_per_repeat) as total_time, " 

254 f"string_agg(distinct osd.scheduling_priority_name, ',') as priorities, ar2.proposal_copy_id, " 

255 # TODO: facility_configuration updates needed 

256 f"string_agg(distinct config.hw_string, ',') as facility_configurations, " 

257 # f"'No Configurations' as facility_configurations, " 

258 f"string_agg(distinct ad.scheduler_internal_comments_to_tac, ',') as scheduler_comments, " 

259 f"sum(priority_a_time) as a_time, sum(priority_b_time) as b_time, " 

260 f"sum(priority_c_time) as c_time, sum(priority_n_time) as n_time " 

261 f"from observation_specification_dispositions osd " 

262 f"inner join allocation_requests ar2 on ar2.allocation_request_id = osd.allocation_request_id " 

263 f"inner join allocation_dispositions ad on ad.allocation_request_id = ar2.allocation_request_id " 

264 f"inner join allocation_versions vers on vers.allocation_version_id = ad.allocation_version_id " 

265 f"left join ({configuration_query}) config on config.osd_id = osd.observation_specification_disposition_id " 

266 f"left join ({osd_a_query}) a on ar2.allocation_request_id = a.allocation_request_id " 

267 f"left join ({osd_b_query}) b on ar2.allocation_request_id = b.allocation_request_id " 

268 f"left join ({osd_c_query}) c on ar2.allocation_request_id = c.allocation_request_id " 

269 f"left join ({osd_n_query}) n on ar2.allocation_request_id = n.allocation_request_id " 

270 ) 

271 if allocation_version_id is not None: 

272 osd_query = ( 

273 f"{osd_query} " 

274 f"where vers.allocation_version_id ={allocation_version_id} " 

275 f"group by proposal_copy_id " 

276 ) 

277 else: 

278 osd_query = ( 

279 f"{osd_query} " 

280 f"where vers.published_destination ='{published_for}' " 

281 f"and vers.facility_id = {facility_id} " 

282 f"group by proposal_copy_id " 

283 ) 

284 

285 # get the total requested time for each proposal copy: 

286 # deterministic context has no targets 

287 allocation_request_time_query = ( 

288 f"select sum(st.requested_time) + sum(rt.requested_time) as ar_requested_time, st.allocation_request_id as allocation_request_id " 

289 f"from science_targets st " 

290 f"left join reference_targets rt on rt.allocation_request_id = st.allocation_request_id " 

291 f"group by st.allocation_request_id " 

292 ) 

293 requested_time_query = ( 

294 f"select sum(ar_requested_time) as total_requested_time, ar.proposal_copy_id " 

295 f"from allocation_requests ar " 

296 f"left join ({allocation_request_time_query}) ar_time on ar_time.allocation_request_id = ar.allocation_request_id " 

297 f"group by ar.proposal_copy_id" 

298 ) 

299 query = ( 

300 f"select {fields} from proposals " 

301 f"inner join proposal_dispositions on proposal_dispositions.proposal_id = proposals.proposal_id " 

302 f"left join ({pi_query}) pi on pi.proposal_id = proposals.proposal_id " 

303 f"left join ({coi_query}) coi on coi.proposal_id = proposals.proposal_id " 

304 f"inner join ({prop_reviews_query}) prop_reviews on prop_reviews.proposal_id = proposals.proposal_id " 

305 f"inner join proposal_copies copies on copies.proposal_copy_id = proposals.observatory_copy_id " 

306 f"inner join proposal_classes classes on classes.proposal_class_id = copies.proposal_class_id " 

307 f"left join ({ar_facilities_query})ar_facs on ar_facs.proposal_copy_id = copies.proposal_copy_id " 

308 f"left join proposal_science_review_panels psrp on psrp.proposal_id = proposals.proposal_id " 

309 f"left join science_review_panels srp on srp.science_review_panel_id = psrp.science_review_panel_id " 

310 f"inner join ({osd_query}) prop_osd on prop_osd.proposal_copy_id = copies.proposal_copy_id " 

311 f"inner join ({requested_time_query}) time on time.proposal_copy_id = copies.proposal_copy_id " 

312 f"where proposals.solicitation_id = {solicitation_id} " 

313 ) 

314 if is_triggered: 

315 query = query + f"and proposals.is_triggered = {is_triggered} " 

316 if is_joint: 

317 query = query + f"and copies.is_joint = {is_joint} " 

318 if proposal_class_id: 

319 query = query + f"and classes.proposal_class_id = {proposal_class_id} " 

320 

321 # useful debugging output: 

322 # print(f"allocation_request_time_query: {allocation_request_time_query}") 

323 # rs = self.session.execute(text(allocation_request_time_query)) 

324 # print(f" result: {[r for r in rs.mappings().all()]}") 

325 # 

326 # st_query = hw_config_query 

327 # print(f"query: {st_query}") 

328 # rs = self.session.execute(text(st_query)) 

329 # print(f" result: {[r for r in rs.mappings().all()]}") 

330 

331 # print(f"coi_query: {coi_query}") 

332 # print(f"prop_reviews_query: {prop_reviews_query}") 

333 # print(f"ar_facilities_query: {ar_facilities_query}") 

334 # print(f"osd_query: {osd_query}") 

335 # rs = self.session.execute(text(osd_query)) 

336 # print(f" result: {[r for r in rs.mappings().all()]}") 

337 

338 # print(f"requested_time_query: {requested_time_query}") 

339 # rs = self.session.execute(text(requested_time_query)) 

340 # print(f" result: {[r for r in rs.mappings().all()]}") 

341 # 

342 # print(f"overall query: {query}") 

343 rs = self.session.execute(text(query)) 

344 

345 return rs.mappings().all() 

346 

347 def search( 

348 self, web_query: str, *, offset: int = 0, limit: int = 0 

349 ) -> tuple[int, int, int, str, list[Row[tuple[ProposalSearchResult, Any]]]]: 

350 query = ( 

351 self.session.query( 

352 ProposalSearchResult, 

353 ts_headline( 

354 "english", 

355 ProposalSearchResult.title + " " + ProposalSearchResult.abstract, 

356 websearch_to_tsquery(web_query), 

357 ), 

358 ) 

359 .where(websearch_to_tsquery(web_query).bool_op("@@")(ProposalSearchResult.summary)) 

360 .order_by(func.ts_rank_cd(ProposalSearchResult.summary, websearch_to_tsquery(web_query))) 

361 ) 

362 

363 total_query = self.session.query( 

364 ProposalSearchResult, 

365 ).where(websearch_to_tsquery(web_query).bool_op("@@")(ProposalSearchResult.summary)) 

366 

367 total_count = total_query.count() 

368 

369 if offset: 

370 query = query.offset(offset) 

371 

372 if limit: 

373 query = query.limit(limit) 

374 

375 return total_count, offset, limit, web_query, query.all()