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 }
})

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?