Skip to main content

WF1-2. Bulk upsert HHs and HH Visits

This job was provided by an OpenFn.org user via the job library API.

Metadata

Key Functions

bulk, join, map, query, JSON

Expression

fn(state => {
if (state.payloads.length == 0)
return { ...state, housevisits: [], households: [] };

const owner_ids = state.payloads.map(data => data.properties.owner_id);
const uniq_owner_ids = [...new Set(owner_ids)];

return { ...state, uniq_owner_ids };
});


fn(state => {
if (state.payloads.length == 0) return state;

return query(
`SELECT CommCare_User_ID__c, Id village, Parent_Geographic_Area__c area, Parent_Geographic_Area__r.Name name, Parent_Geographic_Area__r.Parent_Geographic_Area__c catchment FROM Location__c catchment WHERE CommCare_User_ID__c IN ('${state.uniq_owner_ids.join(
"','"
)}') GROUP BY Id, CommCare_User_ID__c, Parent_Geographic_Area__c, Parent_Geographic_Area__r.Name, Parent_Geographic_Area__r.Parent_Geographic_Area__c`
)(state);
});

fn(state => {
if (state.payloads.length == 0) return state;

console.log('Done querying ✅');

return state;
});

fn(state => {
if (state.payloads.length == 0) return state;

console.log(
'Filtering out unwanted users and applying mapping for households and housevisits'
);

const [reference] = state.references;

//HMN 06/11/2023
// console.log(JSON.stringify(reference.records, null, 2));

const records = reference.records;
const fetchReference = (owner_id, arg) => {
const result =
records && records.length > 0
? records.filter(record => record.CommCare_User_ID__c === owner_id)
: 0;

//TODO: Update default value for 'unknown location' before go-live
return result.length > 0
? result[0][arg]
: 'a000800001tMobaAAC' /*unknown location*/;
};

const supervisorMap = {
community_health_nurse: 'Community Health Nurse',
chw_supervisor: 'CHW Supervisor',
chewschas: 'CHEWs/CHAs',
other: 'Other',
none: 'None',
};

const insuranceMap = {
nhif: 'NHIF',
Linda_mama: 'Linda mama',
other_please_specify_if_active: 'Other',
none: 'None',
};

const households = state.payloads
/* HMN 050723
.filter(
h =>
h.properties.commcare_username !== 'openfn.test' &&
h.properties.commcare_username !== 'test.2021' &&
h.properties.test_user !== 'Yes'
)
*/
.map(h => {
// Special calculations ==================================================
const insuranceStatus = h.properties.health_insurance;
const Health_insurance_type__c =
insuranceStatus && insuranceStatus === 'other_please_specify_if_active'
? 'Other'
: insuranceStatus === 'nhif'
? 'NHIF'
: insuranceStatus === 'Linda_mama' || 'linda_mama'
? 'Linda mama'
: insuranceStatus;

const hhStatus = h.properties.Household_Status;
const Active_Household__c =
hhStatus && hhStatus === 'No'
? false
: hhStatus === 'Yes'
? true
: hhStatus;

const reason = h.properties.Reason_for_Inactive;
const Inactive_Reason__c = reason
? reason.toString().replace(/_/g, ' ')
: null;
//HMN 06/11/2023
// console.log(JSON.stringify(h.properties, null, 2));
/*HMN remove this dependancy 07/07/2023
const chw = h.properties.CHW_ID;
const Household_CHW__c =
chw === 'a030800001zQrk' ? 'a030800001zQrk5' : chw ? chw : undefined;
// =======================================================================
*/

return {
CommCare_Username__c: h.properties.commcare_username,
MOH_household_code__c: h.properties.moh_code,
CommCare_Code__c: h.case_id,
Source__c: true,
// TODO: Prod mapping to add back before go-live =======================
Household_CHW__c: h.properties.CHW_ID, // Uncomment me to go live!
//Household_CHW__c: 'a032400000GHpdsAAD', // Comment me OUT to go live!
// Household_CHW__c: 'a03AW00000643nLYAQ',
// =====================================================================
Catchment__c: fetchReference(h.properties.owner_id, 'catchment'),
Area__c: fetchReference(h.properties.owner_id, 'area'),
Village__c: fetchReference(h.properties.owner_id, 'village'),
Household_Village__c: h.properties.village,
Deaths_in_the_last_6_months__c:
h.properties.deaths_in_past_6_months > 0 ? 'Yes' : 'No',
Access_to_safe_water__c: h.properties.Safe_Water,
Treats_Drinking_Water__c: h.properties.Treats_Drinking_Water,
Tippy_Tap__c: h.properties.Active_Handwashing_Station,
Pit_Latrine__c: h.properties.Functional_Latrine,
Rubbish_Pit__c: h.properties.Rubbish_Pit,
Drying_Rack__c: h.properties.Drying_Rack,
Kitchen_Garden__c: h.properties.Kitchen_Garden,
Cookstove__c: h.properties.Improved_Cooking_Method,
Clothe__c: h.properties.Clothesline,
WASH_Trained__c: h.properties.WASH_Trained,
Uses_ITNs__c: h.properties.ITNs,
Has_muac_tape__c: h.properties.family_muac_tape_available,
Health_insurance__c: h.properties.health_insurace_cover,
Health_insurance_active_status__c: h.properties.healthinsurance_active,
Health_insurance_type__c,
Other_Health_Insurance__c: h.properties.if_other_please_specify,
Work_with_TBA__c: h.properties.tba,
TBA_name__c: h.properties.which_tba,
Last_Modified_Date_CommCare__c: h.server_date_modified, //Need a case property,
Active_Household__c,
Inactive_Reason__c,
Active_in_Nutrition_Program__c:
h.properties.enrolled_in_a_lwala_nutrition_program,
lwala_nutrition_program_enrollment_date__c:
h.properties.lwala_nutrition_program_enrollment_date,
Trained_in_gardening__c: h.properties.household_trained_on_gardening,
household_trained_on_gardening_date__c:
h.properties.when_was_the_household_trained_on_gardening,
Seed_Input_Support__c:
h.properties.household_provided_with_seed_input_support,
household_provided_with_seed_input_suppo__c:
h.properties.when_was_the_household_provided_with_seed_input_support,
MIYCN_Trained__c: h.properties.household_trained_on_MIYC,
// TODO: @Aleksa to find out if Case_Closed_Date__c still exist
Case_Closed_Date__c: h.date_closed //&& h.date_closed == true
? h.date_closed
: undefined
};
});

const housevisits = state.payloads
//HMN remove test user filters
/*
.filter(
h =>
h.properties.commcare_username !== 'openfn.test' &&
h.properties.commcare_username !== 'test.2021' &&
h.properties.test_user !== 'Yes'
)*/
.map(h => {
// Special calculations ==================================================


const visitIdC =
h.case_id + '_' + h.properties.last_form_opened_date_and_time;

const hVstatus = h.properties.Household_Status;
const Active_Household__c =
hVstatus === 'No' ? false : hVstatus === 'Yes' ? true : hVstatus;

const insuranceTypeC = () => {
let status = h.properties.health_insurance;

let value =
status && status !== ''
? status
.replace(/ /gi, ';')
.split(';')
.map(value => {
return insuranceMap[value] || value;
})
: undefined;
return value ? value.join(';') : undefined;
};

const openedC = () => {
const form_opened = h.properties.last_form_opened_date_and_time;

if (form_opened) {
const value1 = form_opened.split('-').slice(0, 2).join('-');
const value2 = form_opened.split('-').slice(2).join('-');
const formattedValue = [value1, value2].join(' ');
return new Date(formattedValue).toISOString();
}
return undefined;
};

return {
CommCare_Username__c: h.properties.commcare_username,
CommCare_Visit_ID__c: visitIdC,
Catchment__c: fetchReference(h.properties.owner_id, 'catchment'),
'Household__r.CommCare_Code__c': h.case_id,
Date__c: h.properties.Date,
Form_Submitted__c: h.properties.last_form_opened_name,
Active_Household__c: Active_Household__c,
Active_in_Nutrition_Program__c:
h.properties.enrolled_in_a_lwala_nutrition_program,
lwala_nutrition_program_enrollment_date__c:
h.properties.lwala_nutrition_program_enrollment_date,
Trained_in_gardening__c: h.properties.household_trained_on_gardening,
household_trained_on_gardening_date__c:
h.properties.when_was_the_household_trained_on_gardening,
Seed_Input_Support__c:
h.properties.household_provided_with_seed_input_support,
household_provided_with_seed_input_suppo__c:
h.properties.when_was_the_household_provided_with_seed_input_support,
MIYCN_Trained__c: h.properties.household_trained_on_MIYCN,
Kitchen_Garden__c: h.properties.Kitchen_Garden,
Access_to_safe_water__c: h.properties.Safe_Water,
Treats_Drinking_Water__c: h.properties.Treats_Drinking_Water,
Tippy_Tap__c: h.properties.Active_Handwashing_Station,
Pit_Latrine__c: h.properties.Functional_Latrine,
Rubbish_Pit__c: h.properties.Rubbish_Pit,
Drying_Rack__c: h.properties.Drying_Rack,
Cookstove__c: h.properties.Improved_Cooking_Method,
Clothe__c: h.properties.Clothesline,
WASH_Trained__c: h.properties.WASH_Trained,
Has_muac_tape__c: h.properties.family_muac_tape_available,
Uses_ITNs__c: h.properties.ITNs,
Supervisor_Visit__c: h.properties.supervisor_visit
? supervisorMap[h.properties.supervisor_visit]
: null,
Health_insurance__c: h.properties.health_insurace_cover,
Health_insurance_active_status__c: h.properties.healthinsurance_active,
Health_insurance_type__c: insuranceTypeC(),

Other_Health_Insurance__c: h.properties.if_other_please_specify,
CommCare_Form_Opened__c: openedC(),

};
});

return { ...state, households, housevisits, fetchReference };
});

//JSON logging of records
//HMN 06/11/2023
/*fn(state => {
console.log('HHs to upsert ::', JSON.stringify(state.households));
return state;
});*/


bulk(
'Household__c',
'upsert',
{
extIdField: 'CommCare_Code__c',
failOnError: true,
allowNoOp: true,
},
state => {
console.log('Bulk upserting households...');
return state.households;
}
);

fn(state => {
if (state.payloads.length == 0) return state;

console.log('house holds bulk upsert done');
return state;
});

// TODO clean up in QA used for troubleshooting
// Uncomment this block to add cooldown
// fn(state => {
// return new Promise((resolve, reject) => {
// setTimeout(() => {
// console.log('4 second cooldown finished.');
// resolve(state);
// }, 4000);
// });
// });

bulk(
'Visit__c',
'upsert',
{
extIdField: 'CommCare_Visit_ID__c',
failOnError: true,
allowNoOp: true,
},
state => {
console.log('Bulk upserting housevisits...');
return state.housevisits;
}
);

fn(state => {
if (state.payloads.length == 0) return state;
console.log('house visits bulk upsert done');
return state;
});

// TODO clean up in QA for troubleshooting
// Uncomment this block to add cooldown
// fn(state => {
// return new Promise((resolve, reject) => {
// setTimeout(() => {
// console.log('Final 4 second cooldown finished.');
// resolve(state);
// }, 4000);
// });
// });