Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Fulcrum webhook endpoint for updating a CartoDB table
<?php
# CartoDB Info
$cartodb_username = 'your-cartodb-username-goes-here';
$cartodb_api_key = 'your-cartodb-api-key-goes-here';
$table = 'your-cartodb-table-goes-here';
# Fulcrum Info
$form_id = 'your-fulcrum-form-id-goes-here';
$fulcrum_api_key = 'your-fulcrum-api-key-goes-here';
//$input = file_get_contents('payload.json'); # local file for testing
$input = file_get_contents('php://input'); # POST data from webhook
$data = json_decode($input, true);
# Build key/data_name lookup arrays for non Section fields
function lookup($array) {
global $data_name, $label, $id;
foreach ($array as $key => $value) {
if (is_array($value)) {
if (isset($value['key']) && $value['type'] !== 'Section') {
$data_name[$value['key']] = $value['data_name'];
$label[$value['key']] = $value['label'];
$id[$value['data_name']] = $value['key'];
} else {
lookup($value);
}
}
}
}
# Fetch form definitions to build key/data_name lookup arrays
$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, FALSE);
curl_setopt($ch, CURLOPT_URL, 'https://api.fulcrumapp.com/api/v2/forms');
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'X-ApiToken: ' . $fulcrum_api_key
));
$forms_json = curl_exec($ch);
$forms = json_decode($forms_json, TRUE);
foreach ($forms as $key => $value) {
if ($key === 'forms') {
foreach ($value as $formKey => $formValue) {
foreach ($formValue as $elementKey => $elementValue) {
if ($elementKey === 'elements') {
# Loop through nested elements arrays for non Section types and build key/data_name lookup array
lookup($elementValue);
}
}
}
}
}
# Make sure it's the form we want
if ($data['data']['form_id'] == $form_id) {
$formArray = array(); # Array to hold form fields
$addressArray = array(); # Array to hold address fields
# Loop through form values and format values
foreach ($data['data']['form_values'] as $key => $value) {
# Get address objects in logical order
if (isset($value['sub_thoroughfare'])) {
array_push($addressArray, $value['sub_thoroughfare']);
}
if (isset($value['thoroughfare'])) {
array_push($addressArray, $value['thoroughfare']);
}
if (isset($value['suite'])) {
array_push($addressArray, $value['suite']);
}
if (isset($value['locality'])) {
array_push($addressArray, $value['locality']);
}
if (isset($value['admin_area'])) {
array_push($addressArray, $value['admin_area']);
}
if (isset($value['sub_admin_area'])) {
array_push($addressArray, $value['sub_admin_area']);
}
if (isset($value['postal_code'])) {
array_push($addressArray, $value['postal_code']);
}
if (isset($value['country'])) {
array_push($addressArray, $value['country']);
}
if (isset($value['sub_thoroughfare']) || isset($value['thoroughfare']) || isset($value['suite']) || isset($value['locality']) || isset($value['admin_area']) || isset($value['sub_admin_area']) || isset($value['postal_code']) || isset($value['country'])) {
$value = implode(' ', array_filter($addressArray));
}
# Join choice values & other values and convert to string
if (isset($value['choice_values']) && is_array($value['choice_values'])) {
if (isset($value['other_values'])) {
$value = array_merge($value['choice_values'], $value['other_values']);
}
$value = implode(', ', $value);
}
# If it's an array of objects (photos or repeatables), just give us a string of the id's
if (is_array($value)) {
$objectArray = array();
foreach ($value as $objectKey => $objectValue) {
if (isset($objectValue['photo_id'])) {
$object['id'] = $objectValue['photo_id'];
}
if (isset($objectValue['id'])) {
$object['id'] = $objectValue['id'];
}
array_push($objectArray, $object['id']);
}
$value = implode(', ', $objectArray);
}
$formArray[$key] = $value;
# Inspect local payload.json for testing
//echo $data_name[$key] . ': ' . $formArray[$key] . '<br>';
}
# Standard Fulcrum fields
$fulcrum_id = $data['data']['id'];
$status = $data['data']['status'];
$version = $data['data']['version'];
$form_id = $data['data']['form_id'];
$form_version = $data['data']['form_version'];
$project_id = $data['data']['project_id'];
$created_at = $data['data']['created_at'];
$updated_at = $data['data']['updated_at'];
$client_created_at = $data['data']['client_created_at'];
$client_updated_at = $data['data']['client_updated_at'];
$created_by = $data['data']['created_by'];
$created_by_id = $data['data']['created_by_id'];
$updated_by = $data['data']['updated_by'];
$updated_by_id = $data['data']['updated_by_id'];
$assigned_to = $data['data']['assigned_to'];
$assigned_to_id = $data['data']['assigned_to_id'];
$latitude = $data['data']['latitude'];
$longitude = $data['data']['longitude'];
$altitude = $data['data']['altitude'];
$speed = $data['data']['speed'];
$course = $data['data']['course'];
$horizontal_accuracy = $data['data']['horizontal_accuracy'];
$vertical_accuracy = $data['data']['vertical_accuracy'];
# Custom form fields- modify with Data Name value from the Fulcrum form builder
# Check if field is in payload and return an empty string if not
function field($data_name) {
global $formArray, $id;
return (isset($formArray[$id[$data_name]]) ? $formArray[$id[$data_name]] : '');
}
$date_of_disaster = field('date_of_disaster');
$type_of_disaster = field('type_of_disaster');
$building_address = field('building_address');
$primary_use = field('primary_use');
$vacant = field('vacant');
$owner_info = field('owner_info');
$damage_type = field('damage_type');
$structural_hazards_overall = field('structural_hazards_overall');
$hazardous_elements = field('hazardous_elements');
$non_structural_hazards = field('non_structural_hazards');
$geo_technical_hazards = field('geo_technical_hazards');
$utilities_disconnected = field('utilities_disconnected');
$has_structure_been_evacuated = field('has_structure_been_evacuated');
$can_use = field('can_use');
$injuries = field('injuries');
$injury_details = field('injury_details');
# Create new CartoDB record (use PostgreSQL dollar quoting)
# FYI- CartoDB seems to convert all fields on imported CSV files to string
if ($data['type'] == 'record.create') {
$sql = "INSERT INTO $table (the_geom, fulcrum_id, latitude, longitude, status, updated_by, date_of_disaster, type_of_disaster, building_address, primary_use, vacant, owner_info, damage_type, structural_hazards_overall, hazardous_elements, non_structural_hazards, geo_technical_hazards, utilities_disconnected, has_structure_been_evacuated, can_use, injuries, injury_details) VALUES (ST_SetSRID(ST_MakePoint($longitude,$latitude),4326), $$$fulcrum_id$$, $$$latitude$$, $$$longitude$$, $$$status$$, $$$updated_by$$, $$$date_of_disaster$$, $$$type_of_disaster$$, $$$building_address$$, $$$primary_use$$, $$$vacant$$, $$$owner_info$$, $$$damage_type$$, $$$structural_hazards_overall$$, $$$hazardous_elements$$, $$$non_structural_hazards$$, $$$geo_technical_hazards$$, $$$utilities_disconnected$$, $$$has_structure_been_evacuated$$, $$$can_use$$, $$$injuries$$, $$$injury_details$$);";
}
# Update existing CartoDB record (use PostgreSQL dollar quoting)
if ($data['type'] == 'record.update') {
$sql = "UPDATE $table SET (the_geom, latitude, longitude, status, updated_by, date_of_disaster, type_of_disaster, building_address, primary_use, vacant, owner_info, damage_type, structural_hazards_overall, hazardous_elements, non_structural_hazards, geo_technical_hazards, utilities_disconnected, has_structure_been_evacuated, can_use, injuries, injury_details) = (ST_SetSRID(ST_MakePoint($longitude,$latitude),4326), $$$latitude$$, $$$longitude$$, $$$status$$, $$$updated_by$$, $$$date_of_disaster$$, $$$type_of_disaster$$, $$$building_address$$, $$$primary_use$$, $$$vacant$$, $$$owner_info$$, $$$damage_type$$, $$$structural_hazards_overall$$, $$$hazardous_elements$$, $$$non_structural_hazards$$, $$$geo_technical_hazards$$, $$$utilities_disconnected$$, $$$has_structure_been_evacuated$$, $$$can_use$$, $$$injuries$$, $$$injury_details$$) WHERE fulcrum_id = $$$fulcrum_id$$;";
}
# Delete existing CartoDB record
if ($data['type'] == 'record.delete') {
$sql = "DELETE FROM $table WHERE fulcrum_id = $$$fulcrum_id$$;";
}
# Wire up cURL to POST SQL to CartoDB
$ch = curl_init('https://'.$cartodb_username.'.cartodb.com/api/v2/sql');
$query = http_build_query(array('q'=>$sql,'api_key'=>$cartodb_api_key));
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, FALSE);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
curl_setopt($ch, CURLOPT_POSTFIELDS, $query);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
$result = curl_exec($ch);
curl_close($ch);
# Write SQL out to file for inspection
$text = fopen('cartodb.sql', 'w+');
fwrite($text, $sql);
fclose($text);
# Write payload out to file for inspection
$json = json_encode($input);
$data = json_decode($json, true);
$payload = fopen('payload.json', 'w+');
fwrite($payload, $data);
fclose($payload);
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.