Coverage for rfpy/api/fetch.py: 97%

1'''

2Functions which fetch objects from the database without checking for

3permissions etc

4'''

5import logging

6from operator import itemgetter

7from rfpy.model.audit.event import EventOrgACL

8from rfpy.model.notify import WebhookSubscription

9from typing import Iterable, List, Sequence, Tuple

11from sqlalchemy import (select, and_, literal, func, or_, case, and_,

12 cast, INTEGER, desc, null)

13from sqlalchemy.sql import text

14from sqlalchemy.orm import (

15 subqueryload, joinedload, lazyload, Bundle, aliased, Query, Session, undefer

16)

17from sqlalchemy.orm.session import object_session

18from sqlalchemy.orm.exc import NoResultFound

20from rfpy.model import (Participant, # nopep8 # noqa: F403

21 ProjectPermission, Section, ProjectWatchList,

22 ProjectNote, SectionPermission, QuestionInstance,

23 Score, TotalWeighting, Weighting,

24 QuestionDefinition, QElement, Answer, AAttachment,

25 AuditEvent, Organisation, Category, WeightingSet,

26 QuestionResponseState, QAttachment, IssueWatchList)

27# Direct imports for IDE typing

28from rfpy.model.project import Project

29from rfpy.model.humans import User

30from rfpy.model.issue import Issue

31from rfpy.model.graph import Edge, RelationshipType

34from rfpy.model.questionnaire import NumberString, from_b36, ResponseStatus # nopep8 # noqa: F403

35from rfpy.model import misc # nopep8 # noqa: F403

36from rfpy.templates import get_template # nopep8 # noqa: F403

37from rfpy.auth.password import validate_hash

40log = logging.getLogger(__name__)

43def user(session: Session, user_id) -> User:

44 '''

45 Fetch a User by ID

46 @raises NoResultFound

47 '''

48 return (session.query(User)

49 .filter(User.id == user_id)

50 .one())

53def organisation(session: Session, org_id) -> Organisation:

54 '''

55 Fetch an Organisation by ID,

57 Raises

58 ------

59 NoResultFound if no Organisation found for the given org_id

60 '''

61 return session.query(Organisation).filter(Organisation.id == org_id).one()

64def user_by_password(session, user_id, password):

65 unauth_user = user(session, user_id)

66 if not unauth_user.password:

67 log.error('User %s has no password, refusing to authenticate', user_id)

68 raise NoResultFound('No matching user found')

69 if not validate_hash(password, unauth_user.password):

70 raise NoResultFound('No matching user found')

71 return unauth_user

74def project(session: Session,

75 project_id: int,

76 with_description: bool = False) -> Project:

77 '''

78 Fetch a Project from the database

80 Parameters

81 ----------

82 session : Session

84 project_id : int

86 Raises

87 ------

88 NoResultFound if no Project found for the given project_id

89 '''

90 q = (session.query(Project)

91 .filter(Project.id == project_id)

92 .options(lazyload(Project.owner_org)))

93 if with_description:

94 q.options(undefer('description'))

95 return q.one()

98def section(session: Session, section_id: int) -> Section:

99 '''

100 Fetch a Section from the database

101

102 Parameters

103 ----------

104 session : Session

105

106 section_id : int

107

108 Raises

109 ------

110 NoResultFound if no Section is found for the given section_id

111 '''

112 return session.query(Section).filter(Section.id == section_id).one()

113

114

115pw_cols = Bundle('listed_project', Project.id, Project.title, Project.deadline,

116 Organisation.name.label("owner_org_name"), Project.status,

117 Organisation.id.label('owner_org_id'), Project.date_created,

118 ProjectWatchList.id.isnot(None).label('is_watched'),

119 ProjectWatchList.date_created.label('watching_since'),

120 single_entity=True)

121

122

123def projects_with_watched(session, user, participant_id=None):

124 org_id = user.org_id

125 if participant_id:

126 org_id = participant_id

127 pq = session.query(pw_cols)\

128 .join(Participant)\

129 .filter(Participant.org_id == org_id)\

130 .outerjoin(ProjectWatchList,

131 and_(ProjectWatchList.user == user,

132 ProjectWatchList.project_id == Project.id))\

133 .outerjoin(Organisation, Project.org_id == Organisation.id)

134 if user.is_restricted:

135 pq = pq.join(ProjectPermission).filter(ProjectPermission.user == user)

136 return pq

137

138

139def category_ids_by_project(session, user):

140 '''Fetch a lookup dictionary mapping project_id: [list of category ids]'''

141 pcq = session.query(func.group_concat(Category.id).label('category_ids'),

142 Project.id.label('project_id'))\

143 .join(Category.projects)\

144 .group_by(Project.id)\

145 .filter(Category.organisation == user.organisation)

146

147 return {r.project_id: [int(cid) for cid in r.category_ids.split(',')] for r in pcq}

148

149

150def issue(session, issue_id) -> Issue:

151 return session.query(Issue).filter(Issue.id == issue_id).one()

152

153

154def section_of_project(project, section_id) -> Section:

155 '''

156 Fetch a section if it belongs to the givenproject.

157 It is assumed the user has access to the project.

158

159 Raises

160 ------

161 NoResultFound

162 '''

163 return project.sections.filter(Section.id == section_id).one()

164

165

166def section_by_id(session, section_id) -> Section:

167 return session.query(Section).filter(Section.id == section_id).one()

168

169

170def sections(project, user):

171 '''

172 Returns a Section query object, filtered by permission

173 if the user is restricted

174 '''

175 if user.is_restricted:

176 return project.sections\

177 .join(SectionPermission)\

178 .filter(SectionPermission.user == user)\

179 .order_by(Section.number)

180 else:

181 return project.sections.order_by(Section.number)

182

183

184def visible_subsections_query(parent: Section, user: User):

185 session = object_session(parent)

186 sq = session.query(Section)\

187 .filter(Section.parent_id == parent.id)\

188 .order_by(Section.number)

189 if user.is_restricted:

190 sq = sq.join(SectionPermission)\

191 .filter(SectionPermission.user == user)

192 return sq

193

194

195def qelement(session, q_element_id) -> QElement:

196 return session.query(QElement).get(q_element_id)

197

198

199def answer(session, answer_id) -> Answer:

200 return session.query(Answer).filter(Answer.id == answer_id).one()

201

202

203def answers_in_issues_query(session, project_id: int, issue_id_set: set):

204 return (session.query(Answer)

205 .join(Issue)

206 .filter(

207 Issue.project_id == project_id,

208 Answer.issue_id.in_(issue_id_set)))

209

210

211def scores(session, project, section, scoreset_id, user, filter_by):

212

213 Score.validate_scoreset(scoreset_id, project)

214 Score.check_view_scores(user, scoreset_id)

215

216 sq = session.query(QuestionInstance.id.label('question_id'),

217 Issue.id.label('issue_id'),

218 literal(scoreset_id).label('scoreset_id')

219 )\

220 .join(Section)\

221 .join(Project, Section.project)\

222 .join(Issue)\

223 .outerjoin(Score, and_(

224 Score.scoreset_id == scoreset_id,

225 QuestionInstance.id == Score.question_instance_id,

226 Issue.id == Score.issue_id))\

227 .filter(

228 Project.id == Issue.project_id,

229 Section.id == section.id,

230 filter_by)\

231 .order_by(QuestionInstance.id.asc(), Issue.id.asc())

232

233 sq = sq.add_columns(Score.score.label('score'))

234 return [s._asdict() for s in sq]

235

236

237def question(session: Session, question_id: int) -> QuestionInstance:

238 '''

239 Fetch a question instance by ID

240 '''

241 return session.query(QuestionInstance).filter(QuestionInstance.id == question_id).one()

242

243

244def question_of_project(project, question_id) -> QuestionInstance:

245 '''

246 Fetch a question instance if it belongs to the given project.

247 It is assumed the user has access to the project.

248 Raises

249 ------

250 NoResultFound

251 '''

252 return project.questions.filter(QuestionInstance.id == question_id).one()

253

254

255def question_of_section(session: Session, section_id: int, question_id: int) -> QuestionInstance:

256 '''

257 Fetch question instance by ID only if it belongs in the section whose ID provided

258 Useful when a sections permission have been checked

259 '''

260 return session.query(QuestionInstance)\

261 .filter(QuestionInstance.id == question_id,

262 QuestionInstance.section_id == section_id)\

263 .one()

264

265

266def get_subsections_recursive(session: Session, section_id: int) -> Query:

267 '''

268 Fetch list of question instance by section ID

269 '''

270 beginning_getter = session.query(

271 Section.id, Section.parent_id,

272 literal(0).label('recursive_depth'),

273 cast(null(), INTEGER).label('parent_lvl_1')

274 ).filter(Section.id == section_id).cte(name='children_for', recursive=True)

275 with_recursive = beginning_getter.union_all(

276 session.query(

277 Section.id, Section.parent_id,

278 (beginning_getter.c.recursive_depth + 1).label('recursive_depth'),

279 case(

280 [(beginning_getter.c.recursive_depth == 0, Section.id)],

281 else_=beginning_getter.c.parent_lvl_1).label('parent_lvl_1')

282 ).filter(Section.parent_id == beginning_getter.c.id))

283 return session.query(with_recursive)

284

285

286def or_create_score(session, project, evaluator, question, score_doc):

287 '''

288 Get issue and question required to access score

289 Do required validation and permission checks

290 '''

291 issue_id, scoreset_id = score_doc['issue_id'], score_doc['scoreset_id']

292 if scoreset_id is None:

293 scoreset_id = ''

294

295 Score.validate_scoreset(scoreset_id, project)

296

297 issue = project.get_issue(issue_id)

298

299 # Fetch or create score object

300 created = False

301 try:

302 score = session.query(Score)\

303 .filter(Score.question_instance_id == question.id,

304 Score.issue_id == issue.id,

305 Score.scoreset_id == scoreset_id).one()

306 except NoResultFound:

307 score = Score(issue=issue, question=question, scoreset_id=scoreset_id)

308 session.add(score)

309 created = True

310

311 return score, created

312

313

314def light_nodes(session, project_id, with_questions=True):

315 q = session.query(Section.id, Section.title,

316 literal('section').label('type'),

317 Section.parent_id,

318 Section.number.label('number'),

319 (func.length(func.ifnull(Section.number, '')) / 2).label('depth'))\

320 .filter(Section.project_id == project_id)

321

322 if with_questions:

323 aq = session.query(QuestionInstance.id, QuestionDefinition.title,

324 literal('question').label('type'),

325 QuestionInstance.section_id.label('parent_id'),

326 QuestionInstance.number.label('number'),

327 (func.length(QuestionInstance.number) / 2).label('depth'))\

328 .join(QuestionDefinition)\

329 .filter(QuestionInstance.project_id == project_id)

330 q = q.union(aq)

331

332 return q.order_by('number')

333

334

335def _nest_qlist_node(stack: list, node):

336 node_dict = {

337 'id': node.id,

338 'title': node.title,

339 'number': node.number.dotted

340 }

341 if not stack:

342 node_dict.update(type='section', subsections=[], questions=[])

343 stack.append(node_dict)

344 return

345 if node.type == 'section':

346 node_dict.update(type='section', subsections=[], questions=[])

347 else:

348 node_dict.update(type='question')

349 node_depth = int(node.depth)

350 leaf_depth = len(stack) - 1

351 if node_depth < leaf_depth:

352 # Going back up

353 while node_depth <= leaf_depth:

354 stack.pop()

355 leaf_depth -= 1

356

357 leaf = stack[-1]

358 if node_depth == leaf_depth:

359 parent = stack[-2]

360 stack.pop()

361 else:

362 # node_depth must be >= leaf_depth after while loop above

363 parent = leaf

364

365 collection = 'subsections' if node.type == 'section' else 'questions'

366 parent[collection].append(node_dict)

367 stack.append(node_dict)

368

369

370def light_tree(session, project_id, with_questions=True):

371 stack = []

372 for node in light_nodes(session, project_id, with_questions=with_questions):

373 _nest_qlist_node(stack, node)

374

375 return stack[0]

376

377

378def _check_total_weights(project, weighting_set_id, session):

379 '''

380 Regenerate TotalWeightings if required

381 @see rfpy.model.questionnaire.TotalWeighting

382 '''

383 if not project.total_weights_exist_for(weighting_set_id):

384 log.info('Regenerating TotalWeightings for %s , WeightingSet: %s',

385 project, weighting_set_id)

386 project.save_total_weights(weighting_set_id)

387 session.flush()

388

389

390def scoring_data(project, scoreset_id=''):

391 '''

392 Returns a list of a score records for the given project and scoring set.

393

394 Each dict has keys: score, number, issue_id

395

396 '''

397 session = object_session(project)

398

399 q = session.query(Score.score,

400 QuestionInstance.id.label('question_id'),

401 QuestionInstance.number,

402 Score.issue_id)\

403 .join(QuestionInstance)\

404 .filter(QuestionInstance.project == project,

405 Score.scoreset_id == scoreset_id)

406

407 return q

408

409

410def old_scoring_data(session, project, weighting_set_id=None):

411 '''

412 Returns a list of a score dicts for the given project and weighting set.

413

414 Each dict has keys: score, scoreSet, number, respondent, weight, weightSet

415

416 'weight' is the absolute weight taken from TotalWeightings table.

417

418 If TotalWeighting records don't exist for the given project/scoreset, they

419 are regenerated.

420

421 N.B. This assumes that TotalWeighting records are *deleted* when weights are edited

422 - this is currently managed in Java land (March 2015)

423 '''

424

425 _check_total_weights(project, weighting_set_id, session)

426

427 tw_sc_join = (QuestionInstance.id == TotalWeighting.question_instance_id)\

428 & (TotalWeighting.project_id == QuestionInstance.project_id)

429

430 q = session.query(Score.score,

431 Score.scoreset_id.label('scoreSet'),

432 QuestionInstance.number,

433 QuestionInstance._weight.label('qiWeight'),

434 QuestionDefinition.weight.label('qdefWeight'),

435 Issue.respondent_id.label('respondent'),

436 Issue.id.label('issueId'),

437 Issue.label.label('label'),

438 TotalWeighting.weight.label("weight"),

439 func.coalesce(WeightingSet.name, "").label("weightSet"))\

440 .join(Issue, Score.issue_id == Issue.id)\

441 .join(QuestionInstance, QuestionDefinition)\

442 .join(TotalWeighting, tw_sc_join)\

443 .outerjoin(WeightingSet, TotalWeighting.weighting_set_id == WeightingSet.id)\

444 .filter(QuestionInstance.project == project)

445

446 return q.all()

447

448

449def participant_notes_query(project):

450 session = object_session(project)

451 pq = session.query(Participant.org_id).filter(

452 Participant.project == project)

453 pn = project\

454 .notes_query\

455 .filter(

456 or_(ProjectNote.private == 0, ProjectNote.org_id.in_(pq))

457 ).order_by(ProjectNote.note_time.desc())

458 return pn

459

460

461def vendor_notes(issue, user):

462 nq = issue.project.notes_query

463 own_org_id = user.org_id

464 return nq.filter(

465 or_(and_(

466 ProjectNote.private == True, # noqa

467 ProjectNote.org_id == own_org_id,

468 ProjectNote.kind == 'RespondentNote' # Posted by this vendor to himself

469 ),

470 and_(

471 ProjectNote.private == False,

472 ProjectNote.kind == 'IssuerNote',

473 or_(

474 ProjectNote.target_org_id == None, # Sent by issuer to all vendors

475 ProjectNote.target_org_id == own_org_id # Sent to just this vendor

476 )

477 ),

478 and_(

479 ProjectNote.private == False,

480 ProjectNote.org_id == own_org_id,

481 ProjectNote.kind == 'RespondentNote' # Posted by this vendor to the issue

482 )

483 )

484 ).order_by(ProjectNote.note_time.desc())

485

486

487def note(project: Project, note_id: int) -> ProjectNote:

488 '''

489 Fetch a ProjectNote by id checking it belongs to the given project but

490 without checking user visibility

491

492 @raises NoResultFound

493 '''

494 return project.notes_query.filter(ProjectNote.id == note_id).one()

495

496

497def answer_attachments_q(project, user):

498 '''

499 Returns a list of AAttachment (Answer Attachment) objects from

500 scoreable issues in the given project

501 '''

502 session = object_session(project)

503 issue_filter = Issue.scoreable_filter(project)

504 q = session.query(AAttachment)\

505 .join(Answer, Issue)\

506 .filter(issue_filter)\

507 .filter(Issue.project_id == project.id)\

508 .options(joinedload('answer'),

509 joinedload('answer.question_instance'),

510 subqueryload('answer.issue'),

511 joinedload('answer.issue.respondent'))

512

513 if user.is_restricted:

514 q = q.join(QuestionInstance, QuestionDefinition, Section, SectionPermission)\

515 .filter(SectionPermission.user == user)

516

517 return q

518

519

520def question_scoresummary(session, user, project, section, scoreset_id=''):

521

522 # Questions

523 questions = session.query(

524 QuestionInstance.id.label('question_id'),

525 Issue.id.label('issue_id'),

526 literal(scoreset_id).label('scoreset_id'))\

527 .join(Section)\

528 .join(Project, Section.project)\

529 .join(Issue)\

530 .outerjoin(Score, and_(

531 Score.scoreset_id == scoreset_id,

532 QuestionInstance.id == Score.question_instance_id,

533 Issue.id == Score.issue_id))\

534 .filter(

535 Issue.project == project,

536 Issue.scoreable_filter(project),

537 Section.id == section.id)

538

539 if user.is_restricted:

540 questions = questions.outerjoin(SectionPermission, and_(

541 SectionPermission.user == user,

542 Section.id == SectionPermission.section_id))\

543 .add_columns(case(

544 [(SectionPermission.section_id != None, Score.score)], # noqa

545 else_=literal('N/A')).label('score'))

546 else:

547 questions = questions.add_columns(Score.score)

548

549 return [row._asdict() for row in questions]

550

551

552def subsection_scoressummary(session, user, project, section, scoreset_id=''):

553 '''

554 Subsections

555 First create a subquery totalling up the counts of questions

556 contained immediately in each section.

557 Exclude all sections the score_user does not have permissions on

558 '''

559 sub = session.query(Section.number.label('number'),

560 Issue.id.label('issue_id'),

561 func.count(QuestionInstance.id).label('q_count'),

562 func.count(Score.score).label('q_scored'),

563 func.coalesce(func.sum(Score.score), 0).label('score')

564 )\

565 .join(Project, Section.project)\

566 .join(Issue)\

567 .outerjoin(QuestionInstance, and_(

568 QuestionInstance.project_id == Project.id,

569 QuestionInstance.section_id == Section.id

570 ))\

571 .outerjoin(Score, and_(

572 Score.scoreset_id == scoreset_id,

573 QuestionInstance.id == Score.question_instance_id,

574 Issue.id == Score.issue_id))\

575 .filter(

576 Issue.project_id == project.id,

577 Issue.scoreable_filter(project))\

578 .group_by(Section.id, Issue.id)

579

580 if user.is_restricted:

581 sub = sub.join(SectionPermission, and_(

582 SectionPermission.user == user,

583 Section.id == SectionPermission.section_id))

584

585 return sub.subquery()

586

587

588def section_scoresummary(session, user, project, section, sub):

589 '''

590 create main query of all immediate subsections in this section

591 and total up the totals from the subsection and child subsections

592 from the subquery. No rows from the subquery indicates the score_user

593 does not have access to any subsections, in this case a score of 'N/A'

594 is returned.

595 '''

596

597 subsections = session.query(Section.id.label('section_id'),

598 Issue.id.label('issue_id'),

599 cast(func.sum(sub.c.q_count),

600 INTEGER).label('questions_count'),

601 cast(func.sum(sub.c.q_scored), INTEGER).label(

602 'questions_scored'),

603 func.coalesce(cast(func.sum(sub.c.score), INTEGER),

604 literal('N/A')).label('score')

605 )\

606 .join(Project, Section.project)\

607 .join(Issue)\

608 .outerjoin(sub, and_(

609 sub.c.number.startswith(Section.number),

610 Issue.id == sub.c.issue_id

611 ))\

612 .filter(

613 Issue.project == project,

614 Issue.scoreable_filter(project),

615 Section.parent_id == section.id)\

616 .group_by(Section.id, Issue.id)

617

618 return [row._asdict() for row in subsections]

619

620

621def issues_for_respondent(session, buyer_user, respondent_id):

622 '''

623 Fetch a list of Issue/Project records for the given respondent_id

624 which are visible to the given user

625 '''

626 cols = (

627 Issue.id.label('issue_id'),

628 Issue.status,

629 Issue.issue_date,

630 Issue.accepted_date,

631 Issue.submitted_date,

632 Issue.deadline,

633 Issue.winloss_exposed,

634 Issue.winloss_expiry,

635 Project.id.label('project_id'),

636 Project.title.label('project_title'),

637 Project.org_id.label('project_owner')

638 )

639 q = session.query(*cols)\

640 .join(Project, Participant)\

641 .filter(

642 Participant.organisation == buyer_user.organisation,

643 Issue.respondent_id == respondent_id)\

644 .order_by(Issue.issue_date.desc())

645 return q.all()

646

647

648def audit_events(organisation, event_type=None):

649

650 eq = organisation.visible_events\

651 .order_by(AuditEvent.id.desc())

652

653 if event_type:

654 return eq.filter(AuditEvent.event_type == event_type)

655 else:

656 return eq

657

658

659def project_audit_events(organisation: Organisation, project: Project, event_type=None):

660 ev_query = audit_events(organisation, event_type=event_type)

661 return ev_query.filter(AuditEvent.project == project)

662

663

664def element_answers(session, user, element_id):

665

666 q = session.query(Answer.id.label('answer_id'),

667 Answer.answer,

668 Issue.respondent_id,

669 Issue.id.label('issue_id'),

670 Project.id.label('project_id'),

671 Project.title.label('project_title'),

672 Project.date_published)\

673 .join(Issue, Issue.id == Answer.issue_id)\

674 .join(Project, Participant)\

675 .filter(

676 Answer.element_id == element_id,

677 Participant.organisation == user.organisation,

678 Issue.status.in_(('Submitted', 'Updateable')))

679

680 return q.all()

681

682

683def score_totals_by_project(session, org_id, project_ids: List):

684

685 project_ids_q = session.query(Participant.project_id)\

686 .filter(Participant.org_id == org_id)\

687 .filter(Participant.project_id.in_(project_ids))\

688 .distinct()

689

690 return session.query(func.sum(Score.score * TotalWeighting.weight)

691 .label('total_weighted_score'),

692 Project.title.label('project_title'),

693 Project.org_id.label('project_owner'),

694 Project.date_published,

695 Project.id.label('project_id'),

696 Issue.respondent_id)\

697 .join(TotalWeighting,

698 Score.question_instance_id == TotalWeighting.question_instance_id)\

699 .join(Issue, Project)\

700 .filter(

701 Score.scoreset_id == '',

702 TotalWeighting.weighting_set_id == None,

703 TotalWeighting.section_id == 0,

704 Project.id.in_(project_ids_q))\

705 .group_by(Project.id, Score.issue_id)\

706 .order_by(Project.id.desc(), text('total_weighted_score desc'))

707

708

709def question_instance_by_number(session, project_id, qnode_number) -> QuestionInstance:

710

711 return session.query(QuestionInstance)\

712 .filter(QuestionInstance.number == qnode_number,

713 QuestionInstance.project_id == project_id)\

714 .one()

715

716

717def score_gaps(issue,

718 weighting_set_id=None,

719 expose_weights=True,

720 show_gap_value=True,

721 debug=False):

722

723 tmpl = get_template('sql/score_gap.sql')

724 sql_script = tmpl.render(expose_weights=expose_weights,

725 show_gap_value=show_gap_value,

726 debug=debug)

727

728 params = {

729 'project_id': issue.project_id,

730 'issue_id': issue.id,

731 'weighting_set_id': weighting_set_id

732 }

733

734 res = []

735 session = object_session(issue)

736

737 if show_gap_value:

738 for q_row in session.execute(sql_script, params):

739 q_dict = dict(q_row)

740 q_dict['number'] = from_b36(q_row.number)

741 res.append(q_dict)

742 else:

743 # Don't show a numerical value for the gap, just =, - or +

744 for q_row in session.execute(sql_script, params):

745 q_dict = dict(q_row)

746 q_dict['number'] = from_b36(q_row.number)

747 score_gap = q_dict['score_gap']

748 if score_gap is None:

749 q_dict['score_gap'] = '=='

750 elif score_gap > 0:

751 q_dict['score_gap'] = '+'

752 elif score_gap == 0:

753 q_dict['score_gap'] = '=='

754 else:

755 # score_gap < 1

756 q_dict['score_gap'] = '-'

757 res.append(q_dict)

758

759 res.sort(key=itemgetter('number'))

760

761 return res

762

763

764def search(session: Session,

765 org_id: str,

766 search_term: str,

767 search_options: Sequence[str],

768 project_id: int, offset: int):

769 '''

770 Run a search query.

771 'search_options' must be a sequence of strings. valid values:

772 "answers", "questions", "notes", "scoreComments"

773 '''

774 available_options = {"answers", "questions", "notes", "scoreComments"}

775

776 if not set(search_options) <= available_options:

777 raise ValueError(

778 f'search_options {search_options} contains values not in {available_options}'

779 )

780

781 tmpl = get_template('sql/evaluator_search.sql')

782 sql = tmpl.render(options=set(search_options), project_id=project_id)

783 sql_text = text(sql)

784 params = {

785 'orgId': org_id,

786 'search_term': misc.clean_search_term(search_term),

787 'offset': int(offset)

788 }

789 hit_list = []

790 for r in session.execute(sql_text, params=params).fetchall():

791 doc = dict(r)

792 try:

793 doc['object_ref'] = from_b36(r.object_ref)

794 except TypeError:

795 pass

796 hit_list.append(doc)

797 return hit_list

798

799

800def project_users(user, project_id, restricted_users_only):

801

802 session = object_session(user)

803 uq = (session.query(User)

804 .join(Organisation)

805 .order_by(Organisation.id, User.id))

806

807 if user.organisation.is_consultant:

808 uq = uq.join(Participant)\

809 .filter(Participant.project_id == project_id)

810 else:

811 uq = uq.filter(User.organisation == user.organisation)

812

813 if restricted_users_only:

814 uq = uq.filter(User.type == 'restricted')

815

816 return uq

817

818

819def _ws_weights(project, weightset_id, parent_section_id=None):

820 'Get Weights for Questions and Sections for the given weightset_id'

821 ws = project.weighting_sets\

822 .filter(WeightingSet.id == weightset_id)\

823 .one()

824

825 questions = []

826 sections = []

827

828 wq = ws.weightings

829

830 if parent_section_id is not None:

831 wq = (wq

832 .outerjoin(Section, Weighting.section_id == Section.id)

833 .outerjoin(QuestionInstance, Weighting.question_instance_id == QuestionInstance.id)

834 .filter(

835 or_(

836 Section.parent_id == parent_section_id,

837 QuestionInstance.section_id == parent_section_id

838 )

839 ))

840

841 for w in wq.with_entities(Weighting.section_id,

842 Weighting.question_instance_id,

843 Weighting.value):

844 if w.section_id:

845 sections.append(

846 dict(section_id=w.section_id, weight=w.value)

847 )

848 else:

849 questions.append(

850 dict(question_id=w.question_instance_id, weight=w.value)

851 )

852

853 return {

854 'questions': questions,

855 'sections': sections

856 }

857

858

859def _default_weights(project, parent_section_id=None):

860 'Default weights for all sections and questions in the project'

861 qiq = project.questions.options(lazyload('question_def.elements'))

862 siq = project.sections

863

864 if parent_section_id is not None:

865 qiq = qiq.filter(QuestionInstance.section_id == parent_section_id)

866 siq = siq.filter(Section.parent_id == parent_section_id)

867

868 return {

869 'questions': [dict(question_id=q.id, weight=q.weight)for q in qiq],

870 'sections': [dict(section_id=s.id, weight=s._weight) for s in

871 siq.with_entities(Section.id, Section._weight)]

872 }

873

874

875def weightings_dict(project, weightset_id=None, parent_section_id=None):

876 '''

877 Get a dictionary of weightings for all sections and

878 questions in the given project,

879 {

880 questions: [{question_id: question_weight}]

881 sections: [{section_id: section_weight}]

882 },

883 for the given project and weightset_id

884 '''

885 if weightset_id is None:

886 return _default_weights(project, parent_section_id=parent_section_id)

887 else:

888 return _ws_weights(project, weightset_id, parent_section_id=parent_section_id)

889

890

891def total_weightings_dict(project, weightset_id=None):

892 '''

893 Get a dictionary of *total* weightings for each section

894 and question in the given project {

895 weightset: {id, name}

896 questions: {question_id: total_question_weight}

897 sections: {section_id: total_section_weight}

898 },

899 for the given project and weightset_id

900 '''

901 q = project.total_weightings\

902 .filter(TotalWeighting.weighting_set_id == weightset_id)\

903 .with_entities(TotalWeighting.question_instance_id,

904 TotalWeighting.section_id,

905 TotalWeighting.weight)

906

907 res = {

908 'questions': {},

909 'sections': {}

910 }

911 sections = res['sections']

912 questions = res['questions']

913

914 for tw in q:

915 if tw.section_id != 0: # zero is the default

916 sections[str(tw.section_id)] = tw.weight

917 else:

918 questions[str(tw.question_instance_id)] = tw.weight

919

920 return res

921

922

923def sec_total_weighting(section, weightset_id=None):

924 'Lookup total weighting as a float for the given section and weightset'

925 session = object_session(section)

926 query = (session

927 .query(TotalWeighting.weight)

928 .filter(TotalWeighting.section_id == section.id)

929 .filter(TotalWeighting.weighting_set_id == weightset_id))

930 tw_value = query.scalar()

931 if tw_value is None:

932 log.warning(

933 f"Replacing totals of weight set #{weightset_id} for {section}")

934 section.project.delete_total_weights(weighting_set_id=weightset_id)

935 section.project.save_total_weights(weighting_set_id=weightset_id)

936 tw_value = query.scalar()

937 return tw_value

938

939

940ElBundle = Bundle('element_bundle', QElement.id, QElement.label, QElement.row,

941 QElement.col, QElement.colspan, QElement.mandatory,

942 QElement.height, QElement.width, QElement.regexp,

943 QElement.el_type.label('el_type'),

944 QElement.rowspan, QElement.choices,

945 QuestionInstance.id.label('question_id'),

946 QuestionInstance.number, QuestionDefinition.title,

947 single_entity=True)

948

949QAttBundle = Bundle('qatt_bundle', QAttachment.id, QAttachment.element_id,

950 QAttachment.size_bytes, QAttachment.filename,

951 QAttachment.mimetype, single_entity=True)

952

953

954def response_states(issue, section_id=None):

955 session = object_session(issue)

956 QRS = QuestionResponseState

957 q = session.query(QRS)\

958 .join(QuestionInstance)\

959 .filter(QRS.issue == issue)

960

961 if section_id is not None:

962 q = q.filter(QuestionInstance.section_id == section_id)

963

964 return {qrs.question_instance_id: qrs for qrs in q}

965

966

967def answered_questions(issue, section_id):

968

969 session = object_session(issue)

970 aq = issue.answers\

971 .join(QuestionInstance)\

972 .filter(QuestionInstance.section_id == section_id)

973

974 answer_lookup = {a.element_id: a.answer for a in aq}

975

976 q_filter = QuestionInstance.section_id == section_id

977 atq = session.query(QAttBundle)\

978 .join(QElement, QuestionDefinition, QuestionInstance)\

979 .filter(QuestionInstance.section_id == section_id)

980

981 qatt_lookup = {qa.element_id: qa._asdict() for qa in atq}

982

983 response_state_lookup = response_states(issue, section_id=section_id)

984

985 q = (session.query(ElBundle)

986 .join(QuestionDefinition, QuestionInstance)

987 .filter(q_filter)

988 .order_by(QuestionInstance.number, QuestionInstance.position,

989 QElement.row, QElement.col))

990

991 return iter_quick_questions(q, answer_lookup,

992 qatt_lookup, response_state_lookup)

993

994

995def iter_quick_questions(elbundle_query, answer_lookup,

996 qatt_lookup, response_state_lookup):

997 '''

998 Builds question dictionaries from ElBundle rows

999

1000 This is about twice as fast as approaches going via the ORM

1001 '''

1002

1003 answerables = {'TX', 'CB', 'CR', 'CC', 'AT'}

1004 labelled = {'LB', 'QA', 'CB'}

1005 current_question = None

1006 current_question_id = None

1007 row_list = None

1008 current_row = None

1009

1010 for el in elbundle_query:

1011 el_type = el.el_type

1012

1013 if el.question_id != current_question_id:

1014 # starting a new question

1015 if current_question is not None:

1016 # if not the first question, yield the previous one

1017 yield current_question

1018

1019 current_question_id = el.question_id

1020 row_list = []

1021 current_row = -1

1022 current_question = {

1023 'title': el.title,

1024 'id': current_question_id,

1025 'number': el.number.dotted,

1026 'elements': row_list

1027 }

1028 rsjs = response_state_lookup[current_question_id].as_dict()

1029 current_question['response_state'] = rsjs

1030

1031 if el.row > current_row:

1032 row_list.append([])

1033

1034 current_row = el.row

1035

1036 el_dict = {

1037 'id': el.id,

1038 'el_type': el.el_type,

1039 'colspan': el.colspan,

1040 'rowspan': el.rowspan

1041 }

1042

1043 if el_type in labelled:

1044 el_dict['label'] = el.label

1045 if el_type == 'QA' and el.id in qatt_lookup:

1046 el_dict['attachment'] = qatt_lookup[el.id]

1047

1048 if el_type in answerables:

1049

1050 el_dict['answer'] = answer_lookup.get(el.id, None)

1051

1052 if el_type in ('CR', 'CC'):

1053 el_dict['choices'] = [{'label': c['label']}

1054 for c in el.choices]

1055

1056 elif el_type == 'TX':

1057 el_dict['height'] = el.height

1058 el_dict['width'] = el.width

1059 el_dict['regexp'] = el.regexp

1060

1061 if el_type != 'CB':

1062 el_dict['mandatory'] = el.mandatory

1063

1064 row_list[-1].append(el_dict)

1065

1066 if current_question is not None:

1067 yield current_question # don't forget the last one!!

1068

1069

1070def answering_stats(issue, section):

1071 QR = QuestionResponseState

1072 q = issue.response_states.group_by(QR.allocated_to, QR.status)

1073 if section.number:

1074 q = q.join(QuestionInstance, Section)\

1075 .filter(Section.number.startswith(section.number))

1076 q = q.with_entities(QR.allocated_to,

1077 QR.status,

1078 func.count(QR.id).label('count'))

1079 return [dict(status=si.status.name,

1080 allocated_to=si.allocated_to,

1081 count=si.count) for si in q]

1082

1083

1084def issue_watchers(session, issue):

1085 cols = (User.id.label('user_id'), User.email, User.fullname,

1086 IssueWatchList.date_created.label('watching_since'))

1087 return session.query(*cols)\

1088 .outerjoin(IssueWatchList,

1089 and_(IssueWatchList.user_id == User.id,

1090 IssueWatchList.issue_id == issue.id))\

1091 .filter(User.organisation == issue.respondent)

1092

1093

1094def project_watchers(session, project: Project):

1095 cols = (User.id.label('user_id'), User.email, User.fullname,

1096 ProjectWatchList.date_created.label('watching_since'))

1097 return (

1098 session.query(*cols).join(ProjectWatchList)

1099 .filter(ProjectWatchList.project_id == project.id)

1100 )

1101

1102

1103def _question_ids_q(session, target_project_id, sec_number=None):

1104 # IDs of all question definitions in target (destination) issue

1105 sel = session.query(QuestionInstance.question_def_id)\

1106 .filter(QuestionInstance.project_id == target_project_id)

1107 if sec_number is not None:

1108 sel = sel.filter(QuestionInstance.number.startswith(str(sec_number)))

1109 return aliased(sel.subquery())

1110

1111

1112def importable_answers(session, target_issue: Issue,

1113 sec_number: str = None) -> Query:

1114 '''Get a count of answered questions in source_issue that can be imported into

1115 target_issue

1116 '''

1117

1118 sel_al = _question_ids_q(

1119 session, target_issue.project_id, sec_number=sec_number)

1120

1121 q = session.query(Issue.id.label('issue_id'), Issue.issue_date, Issue.submitted_date,

1122 Project.title,

1123 func.count(QuestionResponseState.id).label('question_count'))\

1124 .join(QuestionResponseState.question_instance)\

1125 .join(Issue, Project)\

1126 .join(sel_al, sel_al.c.question_def_id == QuestionInstance.question_def_id)\

1127 .filter(Issue.respondent_id == target_issue.respondent_id,

1128 Issue.id != target_issue.id,

1129 Issue.status.in_(['Accepted', 'Submitted', 'Updateable']),

1130 QuestionResponseState.status.in_(

1131 [ResponseStatus.ANSWERED, ResponseStatus.APPROVED])

1132 )\

1133 .group_by(Issue.id)\

1134 .order_by(desc('question_count'))

1135

1136 return q

1137

1138

1139def importable_answer_lookup(session: Session,

1140 source_issue: Issue,

1141 target_project: Project,

1142 sec_number: str):

1143

1144 sel_al = _question_ids_q(session, target_project.id, sec_number=sec_number)

1145 lq = session.query(Answer.element_id,

1146 Answer.answer,

1147 QuestionDefinition.id.label('question_def_id'))\

1148 .join(QElement, Answer.element_id == QElement.id)\

1149 .join(QuestionDefinition, QuestionInstance, QuestionResponseState)\

1150 .join(sel_al, sel_al.c.question_def_id == QuestionInstance.question_def_id)\

1151 .filter(Answer.issue == source_issue,

1152 QuestionInstance.project_id == source_issue.project_id,

1153 QuestionResponseState.issue == source_issue)\

1154 .filter(QuestionResponseState.status.in_([ResponseStatus.ANSWERED,

1155 ResponseStatus.APPROVED]))

1156 return lq

1157

1158

1159def latest_event(session: Session):

1160 return session.query(AuditEvent).order_by(AuditEvent.id.desc()).first()

1161

1162

1163def category_for_user(session: Session, user: User, category_id: int) -> Category:

1164 '''

1165 Fetch the category with given id checking that it belongs to the user's organisation

1166 Returns NoResultFound if category id or user's org_id are wrong

1167 '''

1168 return session.query(Category)\

1169 .filter(user.organisation == Category.organisation)\

1170 .filter(Category.id == category_id)\

1171 .one()

1172

1173

1174def questionnaire_stats(session: Session, project_id: int):

1175 aec = session.query(QElement.el_type, func.count(QElement.id))\

1176 .join(QuestionDefinition, QuestionInstance)\

1177 .filter(QuestionInstance.project_id == project_id)\

1178 .group_by(QElement.el_type)

1179

1180 el_counts = dict(aec)

1181 answerable_count = 0

1182

1183 for answerable_type in QElement.answerable_types:

1184 if answerable_type in el_counts:

1185 answerable_count += el_counts[answerable_type]

1186

1187 el_counts['answerable_elements'] = answerable_count

1188 qi_count = session.query(QuestionInstance.id).filter_by(

1189 project_id=project_id).count()

1190 sec_count = session.query(Section.id).filter_by(

1191 project_id=project_id).count()

1192

1193 return {

1194 'questions': qi_count,

1195 'sections': sec_count,

1196 'elements': el_counts

1197 }

1198

1199

1200def unanswered_mandatory(issue):

1201 '''

1202 Return a query fetching QuestionInstance id and number fields for unanswered, mandatory

1203 questions for the given issue

1204 '''

1205 session = object_session(issue)

1206 return (session.query(QuestionInstance.id, QuestionInstance.number)

1207 .join(QuestionDefinition)

1208 .join(QElement)

1209 .join(QuestionResponseState)

1210 .filter(QElement.mandatory == 1)

1211 .filter(QuestionInstance.project == issue.project)

1212 .filter(QuestionResponseState.status == ResponseStatus.NOT_ANSWERED)

1213 .filter(QuestionResponseState.issue_id == issue.id)

1214 .distinct())

1215

1216

1217def edges_for_org_query(session: Session, org_id: str) -> Iterable[Tuple[Edge, str, Edge]]:

1218 return (session.query(Edge)

1219 .join(RelationshipType, Organisation)

1220 # .options(

1221 # subqueryload(Edge.from_org, Edge.to_org),

1222 # joinedload(Edge.relationship_type)

1223 # )

1224 .filter(RelationshipType.org_id == org_id))

1225

1226

1227def webhooks_for_event(event: AuditEvent) -> List[WebhookSubscription]:

1228 session = object_session(event)

1229 q = (

1230 session.query(WebhookSubscription)

1231 .join(Organisation, EventOrgACL)

1232 .filter(EventOrgACL.event_id == event.id)

1233 .filter(WebhookSubscription.event_type == event.event_type)

1234 )

1235 return q

1236

1237

1238def visible_nodes(session: Session, section: Section, with_questions=True, with_ancestors=False):

1239 if with_ancestors:

1240 regex = NumberString.visible_relatives_regex(section.number)

1241 else:

1242 regex = f"^{section.number}.{{0,2}}$"

1243

1244 q = session.query(Section.id,

1245 Section.title,

1246 Section.description,

1247 literal('section').label('type'),

1248 Section.parent_id, Section.number.label('number'),

1249 (func.length(Section.number) / 2).label('depth'))\

1250 .filter(Section.project_id == section.project_id)\

1251 .filter(Section.number.op('REGEXP')(regex))

1252

1253 if with_questions:

1254 aq = session.query(QuestionInstance.id, QuestionDefinition.title,

1255 literal('').label('description'),

1256 literal('question').label('type'),

1257 QuestionInstance.section_id.label('parent_id'),

1258 QuestionInstance.number.label('number'),

1259 (func.length(QuestionInstance.number) / 2).label('depth'))\

1260 .join(QuestionDefinition)\

1261 .filter(QuestionInstance.project_id == section.project_id)\

1262 .filter(QuestionInstance.section_id == section.id)

1263 q = q.union(aq)

1264 return q.order_by('number')

1265

1266

1267def duplicated_qdefs(

1268 session: Session, destination_project_id: int, source_project_id: int,

1269 src_sections: List[Section],

1270 src_questions: List[QuestionInstance]) -> List[QuestionInstance]:

1271 '''

1272 Returns a list of QuestionInstance objects belonging to the Project given by

1273 destination_project_id and whose QuestionDefintion is shared with QuestionInstances in

1274 src_questions or src_sections

1275 '''

1276

1277 # Find QuestionDefinition ids for all questions to be imported from the source project

1278 condition = []

1279 if src_sections:

1280 regex = "|".join([f"^{s.number}" for s in src_sections])

1281 condition.append(and_(

1282 QuestionInstance.number.op('REGEXP')(regex),

1283 QuestionInstance.project_id == source_project_id

1284 ))

1285

1286 if src_questions:

1287 condition.append(QuestionInstance.id.in_([q.id for q in src_questions]))

1288

1289 source_qids = (select(QuestionInstance.question_def_id).where(or_(*condition)))

1290

1291 # Find QuestionInstances in the destination project that share the same QuestionDefinitions

1292 # as in the source project

1293 qi_query = (

1294 session.query(QuestionInstance)

1295 .filter(QuestionInstance.question_def_id.in_(source_qids))

1296 .filter(QuestionInstance.project_id == destination_project_id)

1297 .options(lazyload(QuestionInstance.question_def)) # Don't eagerload q defs & elements

1298 )

1299

1300 return qi_query.all()