Implement Expiry Check

[briswells]

The expiry check button on the dashboard has no views or routes associated with it. This is a requirement per the original project requirements.

[briswells]

In the backend expiry checks are done randomly at different points. I am thinking implementing a nightly DB job and an additional route that will manually run it which updates expiration is the best way to move forward. Then all routes don't need to worry if things are expired, they can just pull current stock.

[Jooms]

Taking this with @parthpandey1 and @reembot

[Jooms]

Starting todo:

1. Investigate
2. Design
3. Breakdown
4. Build

[Jooms]

Handled 1, 2, and 3 with @parthpandey1 and @reembot.

Details for 1 and 2 here, 3 will follow in another comment

This is the project structure as we understand it.

This is the request flow between the project components we are planning

From the UML Diagram

These are the tables and fields we think are going to be important

[Jooms]

For 3 above, the breakdown, here's the plan.

• @reembot will figure out a strategy for expiration date understanding in TypeScript
  • Right now, the db models are storing a string. We're wondering why it's not a typescript Date. If it must be a string, then we need to know what format it will be saving as.
  • Deliverables:
    • Update model to store dates or report-back with explanation as to why it has to be a string.
    • Update dummy data in sql folder to add an expired and nearly-expired food item.
• @Jooms Will write and SQL query for gathering all the data needed for the FrontEnd.
  • This will be a complex query with multiple joins that returns 0 or more rows for items that are still here that haven't expired
  • Deliverables:
    • SQL statement that retrieves ItemName, Quantity, ExpirationDate, and "ShelfAddress" where it can be found. Filter must only show items that are still in-stock. SQL statement will be recorded in this issue for @parthpandey1 to use.
• @parthpandey1 Will add an API that uses the query @Jooms provides to gather data for expired items, and returns them in properly-formatted JSON.
  • Deliverables:
    • Backend API code update that executes SQL and return data for "expired" items.

TODO: Frontend work to be determined after frontend is enabled. We will likely reach out to the Frontend team to take this piece as we've heard one is already working on it.

We also need to ensure that whomever writes the "Register donated item" Screen sets the expiration correctly. Likely another discussion with the FE team.

[briswells]

Just to add a couple of points to the comments above. Dates are being converted from a string to a date by the models since that is what the old models were expecting, it can easily swap them to use typescript dates.

Manual SQL Queries should be avoided as we are using Sequelize to interface with the database. Table relationships have been programmed into Sequelize allowing you to perform joins etc without writing manual SQL.

[briswells]

Although this would be a good candidate for a Materialized View, which would require a view to be made in the DB with that query and then a read only Sequelize model for the view to be created, but we want to avoid adding SQL to the JS code.

[Jooms]

We will use models instead of SQL for sure, thanks @briswells

[briswells]

If you end up wanting to use a materialize view take a look at how I created the stock view for an example.

[reembot]

Discussed with @briswells DB structure for trans_item model, and it is already storing the expiration field as a Date. I created a PR change the trans_item interface to expect Dates instead of strings, but everything else should be fully operational here (including the current dummy data SQL).