Skip to main content

Q3 2022 Upsert Household & Household Visit in SF

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

Metadata​

  • Name: Q3 2022 Upsert Household & Household Visit in SF
  • Adaptor: @openfn/language-salesforce
  • Adaptor Version: latest
  • Created over 2 years ago
  • Updated 9 months ago
  • Score: 0 (an indicator of how useful this job may be)

Key Functions​

dataValue, field, fields, join, map, query, relationship, upsertIf, Array

Expression​

query(
  `SELECT Id, Parent_Geographic_Area__c, Parent_Geographic_Area__r.Name, Parent_Geographic_Area__r.Parent_Geographic_Area__c FROM Location__c WHERE CommCare_User_ID__c = '${dataValue(
    'properties.owner_id'
  )(state)}'`
);

fn(state => { console.log("query1 done"); return state; });
fn(state => {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('4 second cooldown finished.');
      resolve(state);
    }, 4000);
  });
});

fn(state => ({
  ...state,
  data: {
    ...state.data,
    villageNewId:
      state.references[0].records && state.references[0].records.length !== 0
        ? state.references[0].records[0].Id
        : undefined,
    areaNewId:
      state.references[0].records && state.references[0].records.length !== 0
        ? state.references[0].records[0].Parent_Geographic_Area__c
        : undefined,
    catchmentNewId:
      state.references[0].records && state.references[0].records.length !== 0
        ? (state.references[0].records[0].Parent_Geographic_Area__r 
          ? state.references[0].records[0].Parent_Geographic_Area__r.Parent_Geographic_Area__c
          : undefined)
        : undefined,
  },
}));

upsertIf(
   state.data.properties.commcare_username !== 'openfn.test' &&
    state.data.properties.commcare_username !== 'test.2021' &&
    state.data.properties.test_user  !== 'Yes' ,
  'Household__c',
  'CommCare_Code__c',
  fields(
    field('CommCare_Username__c', dataValue('properties.commcare_username')),
    field('MOH_household_code__c', dataValue('properties.moh_code')),
   field('CommCare_Code__c', dataValue('case_id')),
    field('Source__c', true),
    //field('Household_CHW__c', 'a030Q00000A0jeYQAR'), //sandbox hardcoded mapping
 field('Household_CHW__c', state => {
      var chw = dataValue('properties.CHW_ID')(state);
      return chw === 'a030800001zQrk'
        ? 'a030800001zQrk5'
        : chw
        ? chw
        : undefined;
    }),
   //TODO: Prod mapping to add back before go-live
    field('Catchment__c', dataValue('catchmentNewId')),
    field('Area__c', dataValue('areaNewId')),
    field('Village__c', dataValue('villageNewId')),
    field('Household_Village__c', dataValue('properties.village')),
  //   relationship('Catchment__r', 'Name', state => {
  //     var catchment =
  //       state.data.properties.catchement ||
  //       state.data.properties.catchment_name;
  //     return catchment === '' || catchment === undefined
  //       ? 'Unknown Location'
  //       : catchment;
  //   }), // check
  //   field('Area__c', state => {
  //    // var area = dataValue('properties.Area_Name')(state);
  //     return area === '' || area === undefined ? 'a000Q00000Egmu4' : area;
  //   }),  // Commented out because it was causing a job error 
  //   field('Household_village__c', dataValue('properties.village')),//case property, but not in message
  
  //   field('Village__c',dataValue('properties.village_name')), //lookup
    field('Deaths_in_the_last_6_months__c', state => {
      var death = dataValue(
        'properties.deaths_in_past_6_months'
      )(state);
      return death > 0 ? 'Yes' : 'No';
    }),
    field('Access_to_safe_water__c',dataValue('properties.Safe_Water')),//not coming through
    field('Treats_Drinking_Water__c',dataValue('properties.Treats_Drinking_Water')),//not coming through
    field('Tippy_Tap__c',dataValue('properties.Active_Handwashing_Station')),//not coming through
    field('Pit_Latrine__c',dataValue('properties.Functional_Latrine')),//not coming through
    field('Rubbish_Pit__c',dataValue('properties.Rubbish_Pit')),//not coming through
    field('Drying_Rack__c',dataValue('properties.Drying_Rack')),//not coming through
    field('Kitchen_Garden__c',dataValue('properties.Kitchen_Garden')),//not coming through
    field('Cookstove__c',dataValue('properties.Improved_Cooking_Method')),//not coming through
    field('Clothe__c',dataValue('properties.Clothesline')),//not coming through
    field('WASH_Trained__c',dataValue('properties.WASH_Trained')),//not coming through
    field('Uses_ITNs__c',dataValue('properties.ITNs')),
     field(
      'Has_muac_tape__c',
      dataValue('properties.family_muac_tape_available')
    ),
    //field('Total_household_people__c',dataValue('properties.Total_Number_of_Members')), //not coming through
    field('Health_insurance__c', dataValue('properties.health_insurace_cover')),
    field('Health_insurance_active_status__c',dataValue('properties.healthinsurance_active')),
    field('Health_insurance_type__c', state => {
      var status = dataValue('properties.health_insurance')(state);
      return status && status === 'other_please_specify_if_active'
        ? 'Other'
        : status === 'nhif'
        ? 'NHIF'
        : status === 'Linda_mama' || 'linda_mama'
        ? 'Linda mama'
        : status;
    }),
    field('Other_Health_Insurance__c',dataValue('properties.if_other_please_specify')),
    field('Work_with_TBA__c', dataValue('properties.tba')),
    field('TBA_name__c', dataValue('properties.which_tba')),
    field('Last_Modified_Date_CommCare__c', dataValue('server_date_modified')),//Need a case property),
    field('Active_Household__c', state => {
      var status = dataValue('properties.Household_Status')(state);
      return status && status === 'No'
        ? false
        : status === 'Yes'
        ? true
        : status;
    }),
    // relationship('Head_of_Household__r', 'CommCare_ID__c', dataValue('properties.head_of_household_case_id')),
     field('Inactive_Reason__c', state => {
      var reason = dataValue('properties.Reason_for_Inactive')(state);
      return reason ? reason.toString().replace(/_/g, ' ') : null;
    }),
     field(
      'Active_in_Nutrition_Program__c',
      dataValue(
        'properties.enrolled_in_a_lwala_nutrition_program'
      )
    ),
    field(
      'lwala_nutrition_program_enrollment_date__c',
      dataValue(
        'properties.lwala_nutrition_program_enrollment_date'
      )
    ),
    field(
      'Trained_in_gardening__c',
      dataValue('properties.household_trained_on_gardening')
    ),
    field(
      'household_trained_on_gardening_date__c',
      dataValue(
        'properties.when_was_the_household_trained_on_gardening'
      )
    ),
    field(
      'Seed_Input_Support__c',
      dataValue('properties.household_provided_with_seed_input_support'
      )
    ),
    field(
      'household_provided_with_seed_input_suppo__c',
      dataValue(
        'properties.when_was_the_household_provided_with_seed_input_support'
      )
    ),
    field(
      'MIYCN_Trained__c',
      dataValue('properties.household_trained_on_MIYCN')
    ),
    // not in message:
    // field(
    //   'Kitchen_Garden__c',
    //   dataValue('properties.nutrition_enrollment.household_has_kitchen_garden')
    // ),

    //field('Case_Closed_Date__c', state => {
    //  var closed = dataValue('date_closed')(state); 
    //  var date =  dataValue('server_date_modified')(state); 
    //  return closed && closed == true ? date : undefined; 
   // })
    )
);

fn(state => { console.log("upsertIf1 done"); return state; });
fn(state => {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('4 second cooldown finished.');
      resolve(state);
    }, 4000);
  });
});

//Household Visit
query(
  `SELECT Id, Parent_Geographic_Area__c, Parent_Geographic_Area__r.Name, Parent_Geographic_Area__r.Parent_Geographic_Area__c FROM Location__c WHERE CommCare_User_ID__c = '${dataValue(
    'properties.owner_id'
  )(state)}'`
);

fn(state => { console.log("query2 done"); return state; });
fn(state => {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('4 second cooldown finished.');
      resolve(state);
    }, 4000);
  });
});

fn(state => ({
  ...state,
  data: {
    ...state.data,
    catchmentNewId:
      state.references[0].records && state.references[0].records.length !== 0
        ? (state.references[0].records[0].Parent_Geographic_Area__r 
          ? state.references[0].records[0].Parent_Geographic_Area__r.Parent_Geographic_Area__c
          : undefined)
        : undefined,
  },
}));

fn(state => {
/*  const deaths = state.data.form.household_deaths
    ? state.data.form.household_deaths.deaths
    : '';
  if (deaths !== '' && !Array.isArray(deaths)) {
    state.data.form.household_deaths.deaths = [deaths];
  }*/

  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',
  };

  return { ...state, supervisorMap, insuranceMap };
});

upsertIf(
  // state.data.properties.username !== 'openfn.test' &&
    state.data.properties.username !== 'test.2021' &&
    state.data.properties.test_user  !== 'Yes' ,
  'Visit__c',
  'CommCare_Visit_ID__c',
  fields(
    field('CommCare_Username__c', dataValue('properties.commcare_username')),//
    // field('CommCare_Visit_ID__c', dataValue('id')),
    field('CommCare_Visit_ID__c', state => {
      var case_id = dataValue('case_id')(state);
      var submitted = dataValue('properties.last_form_opened_date_and_time')(state);
      return case_id + '_' +  submitted;
    }),
    // field('Household_CHW__c', 'a030Q00000A0jeY'),
    // field('Catchment__c', dataValue('a000Q00000Egmtk')),
    field('Catchment__c', dataValue('catchmentNewId')),
    // field('Household__c','a010Q00000BL6lT'),
    // field('Household__c', dataValue('form.case.@case_id')),
    relationship(
          'Household__r',
          'CommCare_Code__c',
          dataValue('case_id')),
    field('Date__c',dataValue('properties.Date')),
    field('Form_Submitted__c', dataValue('properties.last_form_opened_name')),

    //field('MOH_household_code__c', state => {
    //  var moh = dataValue('form.Household_Information.moh_code')(state);
    //  var mohLinked = dataValue('form.MOH_household_code_linked')(state);
    // return moh ? moh : mohLinked && mohLinked !== '' ? mohLinked : undefined;
   // }),
    field('Active_Household__c', state => {
      var status = dataValue('properties.Household_Status')(state);
      return status && status === 'No'
        ? false
        : status === 'Yes'
        ? true
        : status;
    }),
    //field('Inactive_Reason__c', state => {
    //  var reason = dataValue('form.Reason_for_Inactive')(state);
    //  return reason ? reason.toString().replace(/_/g, ' ') : null;
    //}),
    //field('Source__c', 1),//
    //relationship(
    //  'Household_CHW__r', 
    //  'CommCare_ID__c', 
    //  dataValue('form.sfid')),TO UPDATE IN PRODUCTION
   // field('Household_village__c', dataValue('form.village')),//
    //New Nutrition Field (MOTG)
    field(
      'Active_in_Nutrition_Program__c',
      dataValue(
        'properties.enrolled_in_a_lwala_nutrition_program'
      )
    ),
    field(
      'lwala_nutrition_program_enrollment_date__c',
      dataValue(
        'properties.lwala_nutrition_program_enrollment_date'
      )
    ),
    field(
      'Trained_in_gardening__c',
      dataValue('properties.household_trained_on_gardening')
    ),
    field(
      'household_trained_on_gardening_date__c',
      dataValue(
        'properties.when_was_the_household_trained_on_gardening'
      )
    ),
    field(
      'Seed_Input_Support__c',
      dataValue(
        'properties.household_provided_with_seed_input_support'
      )
    ),
    field(
      'household_provided_with_seed_input_suppo__c',
      dataValue(
        'properties.when_was_the_household_provided_with_seed_input_support'
      )
    ),
    field(
      'MIYCN_Trained__c',
      dataValue('properties.household_trained_on_MIYCN')
    ),
    field(
      'Kitchen_Garden__c',
      dataValue('properties.Kitchen_Garden')
    ),

    field(
      'Access_to_safe_water__c',
      dataValue('properties.Safe_Water')
    ),
    field(
      'Treats_Drinking_Water__c',
      dataValue('properties.Treats_Drinking_Water')
    ),
    field(
      'Tippy_Tap__c',
      dataValue('properties.Active_Handwashing_Station')
    ),
    field(
      'Pit_Latrine__c',
      dataValue('properties.Functional_Latrine')
    ),
    field(
      'Rubbish_Pit__c',
      dataValue('properties.Rubbish_Pit')
    ),
    field(
      'Drying_Rack__c',
      dataValue('properties.Drying_Rack')
    ),
    field(
      'Kitchen_Garden__c',
      dataValue('properties.Kitchen_Garden')
    ),
    field(
      'Cookstove__c',
      dataValue('properties.Improved_Cooking_Method')
    ),
    field('Clothe__c', dataValue('properties.Clothesline')),
    field(
      'WASH_Trained__c',
      dataValue('properties.WASH_Trained')
    ),
    field(
      'Has_muac_tape__c',
      dataValue('properties.family_muac_tape_available')
    ),
    field('Uses_ITNs__c', dataValue('properties.ITNs')),
    field('Supervisor_Visit__c', state =>
      state.data.properties.supervisor_visit
        ? state.supervisorMap[state.data.properties.supervisor_visit]
        : null
    ),
    field('Health_insurance__c', dataValue('properties.health_insurace_cover')),
    field(
      'Health_insurance_active_status__c',
      dataValue('properties.healthinsurance_active')
    ),
    field('Health_insurance_type__c', state => {
      var status = dataValue('properties.health_insurance')(state);
      var value =
        status && status !== ''
          ? status
              .replace(/ /gi, ';')
              .split(';')
              .map(value => {
                return state.insuranceMap[value] || value;
              })
          : undefined;
      return value ? value.join(';') : undefined;
    }),
    field(
      'Other_Health_Insurance__c',
      dataValue('properties.if_other_please_specify')
    ),
    //field('Last_Modified_Date_CommCare__c', dataValue('server_modified_on')),
    field('CommCare_Form_Opened__c', state=> {
      var form_opened = dataValue('properties.last_form_opened_date_and_time')(state);
      var value1 = form_opened.split('-').slice(0, 2).join('-');
      var value2 = form_opened.split('-').slice(2).join('-');
      var formattedValue = [value1, value2].join(' ');
      return new Date(formattedValue).toISOString();
    }),
    field('Case_Closed_Date__c', state => {
      var closed = dataValue('date_closed')(state);
      var date = dataValue('server_modified_on')(state);
      return closed && closed == true ? date : undefined;
    })
  )
);

fn(state => { console.log("upsertIf2 done"); return state; });
fn(state => {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('Final 4 second cooldown finished.');
      resolve(state);
    }, 4000);
  });
});