Research spike: Querying for projects with recently completed milestone for land use fee paid.

[TylerMatteo]

The goal of this research spike is to show a proof of concept of how we might query for the data we think we'll need for the ZAP Listserv project. One criteria we may use to trigger notifications is to look for projects that have completed certain milestones on a rolling basis (nightly, weekly, etc). In this spike, we will try performing a query on the CRM UAT2 environment using CRM's OData API, the same API we use in the ZAP Search and Applicant Portal APIs.

For this spike, there are three CRM entities to be aware of:

• Projects
• Milestones - Represents the universe of all possible milestones a project might go through
• Project Milestones - Represents each instance of a milestone that a given project is going through. It joins to both the milestones and projects tables.

The query should return a list of projects with associated Project Milestone entities that meet the following criteria:

• The project milestone is associated with the "Prepare Filed Land Use Fee Payment" milestone
• The "Actual Completion Date", CRM field dcp_actualenddate, is a specific date. That date could be passed to the query as a param or simply the day before whenever the query is running.
• The "Status Reason", CRM field statuscode is "Completed"

Possible approaches

I'm open to however the engineer(s) working on this want to tackle this but one approach might be to build on the existing code in the ZAP Search or Applicant Portal APIs that facilitate querying for projects with OData. Another might be to simply build a raw OData query for the this list of projects to call the OData endpoint directly in a tool such as Postman.

Effort

As spikes are meant to be time boxed, I'm suggesting we limit time spent on this to about half a sprint with two engineers collaborating on it.

Desired Outcomes

• [ ] A demoable query returning a list of projects that meet the above criteria
• [ ] Documentation of how you approached the problem and issues you ran into along the way

[horatiorosa]

@TylerMatteo what were the new req for the spike you mentioned late last week?

[TylerMatteo]

Pulling out in favor of #1529