Implement findCapitalProjectByManagingCodeCapitalProjectId endpoint

[TangoYankee]

Acceptance criteria

• [ ] Implement the findCapitalProjectByManagingCodeCapitalProjectId endpoint in compliance with #238
  • should be part of a capital-project domain folder
• [ ] Write end to end tests
• [ ] Write unit tests

helpful code:

Organizing the domain

An example of a domain that is as complex as the capital-project domain the tax lot domain

Writing the sql query

The query for this is complex. It requires three joins and aggregations into summations and sets. The raw sql below will need to be translated into the drizzle version. Drizzle documentation:

• left join
  • example of chaining three left joins: tax lot repository, findZoningDistrictClassesByBbl
• aggregations
  • The sum aggregation can be done with the drizzle sum aggregation helper
  • The array+distinct aggregation will need to use the "magic sql operator":

    sql<array<string>>`ARRAY_AGG(DISTINCT agency_budget.sponsor)`

Raw SQL

SELECT 
    capital_project.id,
    capital_project.managing_code,
    capital_project.description,
    capital_project.managing_agency AS managing_agency_initials,
    capital_project.min_date,
    capital_project.max_date,
    capital_project.category,
    ARRAY_AGG(DISTINCT agency_budget.sponsor) AS sponsoring_agency_initials,
    ARRAY_AGG(DISTINCT agency_budget.type) AS budget_type,
    SUM(capital_commitment_fund.value) AS commitments_total
FROM project
LEFT JOIN commitment ON (
    capital_commitment.managing_code = project.managing_code AND
    capital_commitment.project_id = project.id
    )
LEFT JOIN agency_budget ON (
    capital_commitment.budget_line_code = agency_budget.code
)
LEFT JOIN commitment_fund ON (
    capital_commitment_fund.commitment_id = capital_commitment.id
)
WHERE 
    capital_project.managing_code = '{user supplied managing code}' AND
    capital_project. id = '{user supplied capital project id}' AND
    capital_commitment_fund.category = 'total' 
GROUP BY 
    capital_project.id,
    capital_project.managing_code;

Related tickets

• blocked by #238