search

OpenFn / primero-ona-dashboard

UNICEF Cambodia integration between Primero and ONA Dashboard
https://openfn.github.io/primero-ona-dashboard/
0 stars 1 forks source link

Getting data from Primero to load to ONA Database #4

Closed daissatou2 closed 2 years ago

daissatou2 commented 3 years ago

Feature Request

Background, context, and business value

OpenFn will check Primero for data once every year in order to extract indicator results that will be reported on and visualized in an external DB.

Create Jobs 1 & 2 in which OpenFn gets cases and services from Primero, maps the Primero extract and upserts to the ONA Postgres database.

  1. Get referral data twice (there are two Primero indicators):
    • GET /api/v2/cases -- where "type_of_case" = "Children Undergoing Reintegration"
    • GET /api/v2/cases -- where where Age < 18 years
    • Sample response: Primero responds with list of cases (example response: list_cases_primero.json)
  2. Map data to ONA database using mapping sheet under the "Mapping to DB" tabs
  3. Upsert cases and referrals in ONA database

See data flow diagram.

state.json

Job 1: Get cases from Primero

{
  "configuration": ["SEE LAST PASS: 'Primero V2 Cambodia Demo"],
  "data": {"data will be generated from GET request"},
  "cursor": "cases created_at > cursorDateTimeYearStart && cases created_at < cursorDateTimeYearEnd"
}

Example for the cursor above: If the job cron trigger runs on Jan 1, 2022, we want to GET cases that would have been created before Jan 1, 2022 but after Jan 1, 2021.

Job 2: Load to DB

{
  "configuration": ["SEE LAST PASS: 'ONA Test Database"],
  "data": {"data will be generated from GET request"},
}

adaptor

Primero PostgreSQL

expression.js

2 new jobs to create in a new dashboard directory in this repo.

  1. GET cases from Primero
  2. Map Primero response and upload to ONA database

trigger

  1. Cron timer
  2. Flow jobs
aleksa-krolls commented 3 years ago

@daissatou2 Responding to your AK please advise requests :)

  1. Please take a first crack at defining the specifications for case filtering. Phanneth explained that on the call... go over our notes on the indicators sheet to see if you can propose the logic here.
  2. When we load to ONA... this will be a direct DB connection (no API interaction... so no need to use POST). Rather, we want to define the DB operation that is best to use... so what makes sense here? create, update, upsert?
aleksa-krolls commented 3 years ago

@daissatou2 for things like "Type of Case" = "Children Undergoing Reintegration", Mamadou will need the db field name and value. If this is not provided by Phanneth & Mohan in the mapping sheet, then follow up with them to get the necessary info so that you can finalize this issue spec.

daissatou2 commented 2 years ago

@aleksa-krolls can you help me understand what should be added to the "data" and "cursor" sections here?

daissatou2 commented 2 years ago

@aleksa-krolls this is ready for you

aleksa-krolls commented 2 years ago

@daissatou2 Nearly there. Some feedback...

  1. When we load to the DB, what operation (create? upsert?) do you suggest we use? And what are the unique identifiers for each table we are modifying?
  2. Left you a couple of comments in the mapping sheet. You may need to ask ONA team to add a unique column for services... also are you able to access the DB using DBeaver? It might be worth checking if they've added unique constraints for case_id... bc we might need to ask them to add this too so that we can upsert.
  3. Added a trigger section - important to ask for "flow" jobs
  4. For state, see how I updated your issue vs. what you had before (see screenshot) Screen Shot 2021-12-09 at 11 55 48 AM

Once feedback has been addressed, okay to move to backlog!

daissatou2 commented 2 years ago
  1. Added the unique identifiers
  2. Addressed the mapping questions & set up the db
elias-ba commented 2 years ago

Hello @daissatou2 I am taking over this issue, would you mind a quick talk in implementation to make sure I understand what to do here ?

aleksa-krolls commented 2 years ago

@daissatou2 can you please sync up with @elias-ba in the morning to talk this through?

elias-ba commented 2 years ago

@daissatou2 what's the equivalent value of type_of_placement in the ONA Database ? The mapping excel sheet has an empty value. What should I do in that case ?

daissatou2 commented 2 years ago

@elias-ba placement_type has been added to the mappings.

daissatou2 commented 2 years ago

@elias-ba we have created a separate repo for this integration: https://github.com/OpenFn/primero-ona-dashboard Please add the code for this issue to that repo. Any future issues relating to this integration will also be added to that repo.

elias-ba commented 2 years ago

Thanks @daissatou2. I was working in the Unicef-Cambodia repo but I will move the code to that new repo.

elias-ba commented 2 years ago

@daissatou2 I am done write the expressions for this issue.

  1. Get cases: https://github.com/OpenFn/primero-ona-dashboard/blob/main/jobs/getCasesFromPrimero.js
  2. Apply mappping and upsert cases: https://github.com/OpenFn/primero-ona-dashboard/blob/main/jobs/upsertCasesToONA.js

I am not able to test the upsert operation locally, it seems like the ONA database is not up and running. Is there any way to test this on platform ?

elias-ba commented 2 years ago

@daissatou2 @aleksa-krolls do we want to keep a cursor and get newest cases or we can get all cases matching the conditions everytime.

elias-ba commented 2 years ago

I have tested this in my local environment and it's working fine. I am now moving it in Review. Please @daissatou2 test it on platform and let me know if there's any additional thing to do on it. Thanks

aleksa-krolls commented 2 years ago

@elias-ba I believe we want to get cases where created_at is within a certain date range... see Aicha's notes on the issue: 

Example for the cursor above: If the job cron trigger runs on Jan 1, 2022, we want to GET cases that would have been created before Jan 1, 2022 but after Jan 1, 2021.

I don't see any date filter currently applied to the GET request: https://github.com/OpenFn/primero-ona-dashboard/blob/main/jobs/getCasesFromPrimero.js

cc @daissatou2

elias-ba commented 2 years ago

Thanks @aleksa-krolls, currently I have implemented no cursor. Is there something missing in @daissatou2's comment ? I can't understand it very well. Is there a not or something like that missing ?

aleksa-krolls commented 2 years ago

@elias-ba it means we need a date filter added like this where we only get cases if they were created after a specific date...

created_at: '2021-01-01T00:00:00.000Z'

@daissatou2 pls get on with Elias tomorrow morning to review and make sure this is working.

elias-ba commented 2 years ago

Thanks @aleksa-krolls this is very clear. @daissatou2 I am going to update the job expression and add that filter then tomorrow morning we can review the functionality together.

elias-ba commented 2 years ago

Hey @daissatou2 I have implemented a date filter on the created_at field to match cases between a starting date and an ending date. Could you check please if this is what you expected ?

elias-ba commented 2 years ago

@daissatou2 please don't waste your time testing this right now. @taylordowns2000 and I are working on it. We'll keep you posted.

elias-ba commented 2 years ago

@daissatou2 this is now done and ready for testing. Let me know for any feed-back.