Get Patients from Postgres table patient and transform fields according to https://docs.google.com/spreadsheets/d/19ZJaudhQXy3J0lKcm5XdK3_ULNjDVMXaG7tuwlyc1zo/edit#gid=1730011001. Upload results to Salesforce object Indicator_Result__c

createResults.js

each('$.records[*]', 
  create('Indicator_Result__c', $.data)
)

// [R/T] ✘ AdaptorError: The requested resource does not exist
bulk(
    'Indicator_Result__c',
    'upsert', 
    {extIdField: 'Result_UID__c', failOnError: true, allowNoOp: true},
    state => state.records
);

getPatientRows.js

sql(state => "SELECT * FROM patient");


sql(state => "SELECT COUNT(patient_name) as num_enrolled_patients, village_name, registration_date FROM patient WHERE enrolled_in_program = 'yes' GROUP BY village_name, registration_date;");


sql(state => "SELECT SUM(opv_doses_given) as num_opv_doses_given, village_name, registration_date FROM patient GROUP BY village_name, registration_date;");


fn(state => {
    const geoMapping = {'Kalapata': 'Uganda', "Conakry": "Guinea", "Kalaa": "Guinea"}
    const getYear = date => {
        let dateObj = new Date(date);
        return dateObj.getFullYear();
    }
    const makeUID = arr => arr.join("_").split(" ").join("_")
    let enrolledList = state.references.at(-1).map(i => {
        let reportingPeriod = getYear(i.registration_date)
        let geoArea = geoMapping[i.village_name]
        return {
            Reporting_Period__c : reportingPeriod,
            Geographic_Area__c : geoArea,
            "Indicator__r.ExtId__c" : "1001",
            Value__c: i.num_enrolled_patients,
            Result_UID__c: makeUID(["1001", reportingPeriod, geoArea]) 
        }  
    })
    
    let opvDoseList = state.data.map(i => {
        let reportingPeriod = getYear(i.registration_date)
        let geoArea = geoMapping[i.village_name]
        return {
            Reporting_Period__c : reportingPeriod,
            Geographic_Area__c : geoArea,
            "Indicator__r.ExtId__c": "1002",
            Value__c: i.num_opv_doses_given,
            Result_UID__c: makeUID(["1002", reportingPeriod, geoArea])
        }
    })

    opvDoseList.forEach(element => {
        enrolledList.push(element)
    });
    let records = enrolledList
    return { ...state, records }
})

@dcjohnson24 here is the quick feedback on Day2 homework

1st. If you not using any value from state, you don't have to reference state when using sql function

See example below 👇

sql("SELECT * FROM patient");

2nd. Create a separate step for mapping and data transformation
This is one of the best practices that we encourage developer to follow. It will make it easier to maintain and scale your workflow in a long term.

Eg: Using common adaptor to create mapping for patients records

const geoMapping = { Kalapata: "Uganda", Conakry: "Guinea", Kalaa: "Guinea" };
const getYear = (date) => {
  const dateObj = new Date(date);
  return dateObj.getFullYear();
};
const makeUID = (arr) => arr.join("_").split(" ").join("_");

fn((state) => {
  let enrolledList = state.references.at(-1).map((i) => {
    let reportingPeriod = getYear(i.registration_date);
    let geoArea = geoMapping[i.village_name];
    return {
      Reporting_Period__c: reportingPeriod,
      Geographic_Area__c: geoArea,
      "Indicator__r.ExtId__c": "1001",
      Value__c: i.num_enrolled_patients,
      Result_UID__c: makeUID(["1001", reportingPeriod, geoArea]),
    };
  });

  let opvDoseList = state.data.map((i) => {
    let reportingPeriod = getYear(i.registration_date);
    let geoArea = geoMapping[i.village_name];
    return {
      Reporting_Period__c: reportingPeriod,
      Geographic_Area__c: geoArea,
      "Indicator__r.ExtId__c": "1002",
      Value__c: i.num_opv_doses_given,
      Result_UID__c: makeUID(["1002", reportingPeriod, geoArea]),
    };
  });

  opvDoseList.forEach((element) => {
    enrolledList.push(element);
  });
  let records = enrolledList;
  return { ...state, records };
}); 

But the mapping looks wrong, we will chat more during our call today

Thanks for the feedback. When I use sql("SELECT * FROM patient");, for example, I get the error

TypeError: sqlQuery is not a function
    at /tmp/openfn/repo/node_modules/@openfn/language-postgresql_4.1.11/dist/index.cjs:206:20
    at file:///usr/local/share/npm-global/lib/node_modules/@openfn/cli/node_modules/@openfn/runtime/dist/index.js:587:26
    at async file:///usr/local/share/npm-global/lib/node_modules/@openfn/cli/node_modules/@openfn/runtime/dist/index.js:551:20
Unhandled error in the operations. Exiting process.

Here are the versions I am using:

[CLI] ♦ Versions:
         ▸ node.js     20.12.0
         ▸ cli         1.2.3

Hiya @dcjohnson24 here is the improvements on day 2 homework

getPatients.js

sql(
  state =>
    "SELECT COUNT(patient_name) as num_enrolled_patients, village_name, registration_date FROM patient WHERE enrolled_in_program = 'yes' GROUP BY village_name, registration_date;"
);

fn(state => {
  state.enrolled_patients = state.data;
  return state;
});

mapPatients.js

const geoMapping = { Kalapata: 'Uganda', Conakry: 'Guinea', Kalaa: 'Guinea' };
const getYear = date => {
  const dateObj = new Date(date);
  return dateObj.getFullYear();
};

const makeUID = arr => arr.join('_').split(' ').join('_');

fn(state => {
  state.records = [1001, 1002]
    .map(indicator => {
      state.enrolled_patients.map(patient => {
        const reportingPeriod = getYear(patient.registration_date);
        const geoArea = geoMapping[patient.village_name];
        return {
          vera__Reporting_Period__c: reportingPeriod,
          vera__Geographic_Area__c: geoArea,
          'vera__Indicator__r.vera__ExtId__c': indicator,
          vera__Value__c: patient.num_enrolled_patients,
          vera__Result_UID__c: makeUID([indicator, reportingPeriod, geoArea]),
        };
      });
    })
    .flat();

  return state;
});

syncPatients.js

bulk(
  'vera__Indicator_Result__c',
  'upsert',
  { extIdField: 'vera__Result_UID__c', failOnError: true, allowNoOp: true },
  state => state.records
);

Thanks for the feedback. When I run this, I get the error

✘ Failed step syncPatients after 1.342s
[R/T] ✘ TypeError: TypeError: Cannot convert undefined or null to object

Also, to include the number of opv doses given, should getPatients.js be changed to

sql(state => "SELECT COUNT(patient_name) as num_enrolled_patients, village_name, registration_date FROM patient WHERE enrolled_in_program = 'yes' GROUP BY village_name, registration_date;");
fn(state => {
    state.enrolled_patients = state.data;
    return state;
})

sql(state => "SELECT SUM(opv_doses_given) as num_opv_doses_given, village_name, registration_date FROM patient GROUP BY village_name, registration_date;");

fn(state => {
    state.num_opv_doses = state.data;
    return state;
})

and mapPatients.js be changed to

const geoMapping = { Kalapata: 'Uganda', Conakry: 'Guinea', Kalaa: 'Guinea' };
const getYear = date => {
  const dateObj = new Date(date);
  return dateObj.getFullYear();
};

const makeUID = arr => arr.join('_').split(' ').join('_');

fn(state => {
    state.records = [1001, 1002]
      .map(indicator => {
        state.enrolled_patients.map(patient => {
          const reportingPeriod = getYear(patient.registration_date);
          const geoArea = geoMapping[patient.village_name];
          return {
            vera__Reporting_Period__c: reportingPeriod,
            vera__Geographic_Area__c: geoArea,
            'vera__Indicator__r.vera__ExtId__c': indicator,
            vera__Value__c: patient.num_enrolled_patients,
            vera__Result_UID__c: makeUID([indicator, reportingPeriod, geoArea]),
          };
        });
      })
      .flat();
    state.records.push([1001, 1002].map(indicator => {
        state.num_opv_doses.map(dose => {
            const reportingPeriod = getYear(dose.registration_date);
            const geoArea = geoMapping[dose.village_name];
            return {
                vera__Reporting_Period__c: reportingPeriod,
                vera__Geographic_Area__c: geoArea,
                'vera__Indicator__r.vera__ExtId__c': indicator,
                vera__Value__c: dose.num_opv_doses,
                vera__Result_UID__c: makeUID([indicator, reportingPeriod, geoArea]),
            };
        });
    }).flat());
  
    return state;
  });

so that indicator 1001 has the number of patients enrolled and 1002 has the number of opv doses given?