209 lines
8.2 KiB
JavaScript
209 lines
8.2 KiB
JavaScript
// db/activities.js
|
|
const knex = require('./knex');
|
|
|
|
/* ============================================================
|
|
INSERT OR UPDATE ACTIVITY
|
|
============================================================ */
|
|
async function insertOrUpdateActivity(row) {
|
|
const exists = await knex('activities').where({ id: row.id }).first();
|
|
|
|
if (!exists) {
|
|
await knex('activities').insert({
|
|
id: row.id,
|
|
user: row.user,
|
|
cartella: row.cartella,
|
|
|
|
file_name: row.file_name,
|
|
file_extension: row.file_extension,
|
|
format: row.format,
|
|
display_name: row.display_name,
|
|
route_name: row.route_name,
|
|
title: row.title,
|
|
description: row.description,
|
|
notes: row.notes,
|
|
sport: row.sport,
|
|
activity_type: row.activity_type,
|
|
device_manufacturer: row.device_manufacturer,
|
|
device_model: row.device_model,
|
|
source_app: row.source_app,
|
|
|
|
start_time_millis: row.start_time_millis,
|
|
end_time_millis: row.end_time_millis,
|
|
duration_millis: row.duration_millis,
|
|
|
|
distance_meters: row.distance_meters,
|
|
elevation_gain_meters: row.elevation_gain_meters,
|
|
elevation_loss_meters: row.elevation_loss_meters,
|
|
|
|
min_elevation_meters: row.min_elevation_meters,
|
|
max_elevation_meters: row.max_elevation_meters,
|
|
|
|
file_distance_meters: row.file_distance_meters,
|
|
file_elevation_gain_meters: row.file_elevation_gain_meters,
|
|
|
|
computed_distance_meters: row.computed_distance_meters,
|
|
computed_elevation_gain_meters: row.computed_elevation_gain_meters,
|
|
|
|
average_speed_mps: row.average_speed_mps,
|
|
max_speed_mps: row.max_speed_mps,
|
|
average_pace_sec_per_km: row.average_pace_sec_per_km,
|
|
|
|
average_heart_rate: row.average_heart_rate,
|
|
max_heart_rate: row.max_heart_rate,
|
|
average_cadence: row.average_cadence,
|
|
max_cadence: row.max_cadence,
|
|
|
|
calories: row.calories,
|
|
power_avg: row.power_avg,
|
|
power_max: row.power_max,
|
|
|
|
point_count: row.point_count,
|
|
segment_count: row.segment_count,
|
|
lap_count: row.lap_count,
|
|
|
|
min_latitude: row.min_latitude,
|
|
max_latitude: row.max_latitude,
|
|
min_longitude: row.min_longitude,
|
|
max_longitude: row.max_longitude,
|
|
center_latitude: row.center_latitude,
|
|
center_longitude: row.center_longitude,
|
|
|
|
size_bytes: row.size_bytes,
|
|
last_modified_millis: row.last_modified_millis,
|
|
date_added_millis: row.date_added_millis,
|
|
date_modified_millis: row.date_modified_millis,
|
|
last_scan_millis: row.last_scan_millis,
|
|
|
|
is_favorite: row.is_favorite,
|
|
is_hidden: row.is_hidden,
|
|
rating: row.rating,
|
|
|
|
tags: row.tags ? JSON.stringify(row.tags) : null,
|
|
scan_status: row.scan_status,
|
|
scan_error: row.scan_error,
|
|
diagnostics: row.diagnostics ? JSON.stringify(row.diagnostics) : null,
|
|
location: row.location ? JSON.stringify(row.location) : null,
|
|
|
|
created_at: knex.fn.now(),
|
|
updated_at: knex.fn.now()
|
|
});
|
|
} else {
|
|
await knex('activities').where({ id: row.id }).update({
|
|
// aggiorna solo i campi che possono cambiare
|
|
file_name: row.file_name || exists.file_name,
|
|
file_extension: row.file_extension || exists.file_extension,
|
|
format: row.format || exists.format,
|
|
display_name: row.display_name || exists.display_name,
|
|
route_name: row.route_name || exists.route_name,
|
|
title: row.title || exists.title,
|
|
description: row.description || exists.description,
|
|
notes: row.notes || exists.notes,
|
|
sport: row.sport || exists.sport,
|
|
activity_type: row.activity_type || exists.activity_type,
|
|
device_manufacturer: row.device_manufacturer || exists.device_manufacturer,
|
|
device_model: row.device_model || exists.device_model,
|
|
source_app: row.source_app || exists.source_app,
|
|
|
|
start_time_millis: row.start_time_millis || exists.start_time_millis,
|
|
end_time_millis: row.end_time_millis || exists.end_time_millis,
|
|
duration_millis: row.duration_millis || exists.duration_millis,
|
|
|
|
distance_meters: row.distance_meters || exists.distance_meters,
|
|
elevation_gain_meters: row.elevation_gain_meters || exists.elevation_gain_meters,
|
|
elevation_loss_meters: row.elevation_loss_meters || exists.elevation_loss_meters,
|
|
|
|
min_elevation_meters: row.min_elevation_meters || exists.min_elevation_meters,
|
|
max_elevation_meters: row.max_elevation_meters || exists.max_elevation_meters,
|
|
|
|
file_distance_meters: row.file_distance_meters || exists.file_distance_meters,
|
|
file_elevation_gain_meters: row.file_elevation_gain_meters || exists.file_elevation_gain_meters,
|
|
|
|
computed_distance_meters: row.computed_distance_meters || exists.computed_distance_meters,
|
|
computed_elevation_gain_meters: row.computed_elevation_gain_meters || exists.computed_elevation_gain_meters,
|
|
|
|
average_speed_mps: row.average_speed_mps || exists.average_speed_mps,
|
|
max_speed_mps: row.max_speed_mps || exists.max_speed_mps,
|
|
average_pace_sec_per_km: row.average_pace_sec_per_km || exists.average_pace_sec_per_km,
|
|
|
|
average_heart_rate: row.average_heart_rate || exists.average_heart_rate,
|
|
max_heart_rate: row.max_heart_rate || exists.max_heart_rate,
|
|
average_cadence: row.average_cadence || exists.average_cadence,
|
|
max_cadence: row.max_cadence || exists.max_cadence,
|
|
|
|
calories: row.calories || exists.calories,
|
|
power_avg: row.power_avg || exists.power_avg,
|
|
power_max: row.power_max || exists.power_max,
|
|
|
|
point_count: row.point_count || exists.point_count,
|
|
segment_count: row.segment_count || exists.segment_count,
|
|
lap_count: row.lap_count || exists.lap_count,
|
|
|
|
min_latitude: row.min_latitude || exists.min_latitude,
|
|
max_latitude: row.max_latitude || exists.max_latitude,
|
|
min_longitude: row.min_longitude || exists.min_longitude,
|
|
max_longitude: row.max_longitude || exists.max_longitude,
|
|
center_latitude: row.center_latitude || exists.center_latitude,
|
|
center_longitude: row.center_longitude || exists.center_longitude,
|
|
|
|
size_bytes: row.size_bytes || exists.size_bytes,
|
|
last_modified_millis: row.last_modified_millis || exists.last_modified_millis,
|
|
date_added_millis: row.date_added_millis || exists.date_added_millis,
|
|
date_modified_millis: row.date_modified_millis || exists.date_modified_millis,
|
|
last_scan_millis: row.last_scan_millis || exists.last_scan_millis,
|
|
|
|
is_favorite: row.is_favorite ?? exists.is_favorite,
|
|
is_hidden: row.is_hidden ?? exists.is_hidden,
|
|
rating: row.rating ?? exists.rating,
|
|
|
|
tags: row.tags ? JSON.stringify(row.tags) : exists.tags,
|
|
scan_status: row.scan_status || exists.scan_status,
|
|
scan_error: row.scan_error || exists.scan_error,
|
|
diagnostics: row.diagnostics ? JSON.stringify(row.diagnostics) : exists.diagnostics,
|
|
location: row.location ? JSON.stringify(row.location) : exists.location,
|
|
|
|
updated_at: knex.fn.now()
|
|
});
|
|
}
|
|
}
|
|
|
|
/* ============================================================
|
|
HARD DELETE
|
|
============================================================ */
|
|
async function markActivityDeletedHard(id, user) {
|
|
await knex('activities').where({ id, user }).del();
|
|
await knex('deleted_hard_activities')
|
|
.insert({ id, user, deleted_at: knex.fn.now() })
|
|
.catch(() => {});
|
|
}
|
|
|
|
/* ============================================================
|
|
CHANGES SINCE
|
|
============================================================ */
|
|
async function getActivitiesSince(user, sinceIso, limit = 1000) {
|
|
return knex('activities')
|
|
.where({ user })
|
|
.andWhere('updated_at', '>', sinceIso)
|
|
.orderBy('updated_at', 'asc')
|
|
.limit(limit)
|
|
.select('*');
|
|
}
|
|
|
|
/* ============================================================
|
|
DELETED HARD SINCE
|
|
============================================================ */
|
|
async function getDeletedHardActivitiesSince(user, sinceIso) {
|
|
return knex('deleted_hard_activities')
|
|
.where({ user })
|
|
.andWhere('deleted_at', '>', sinceIso)
|
|
.select('id', 'deleted_at');
|
|
}
|
|
|
|
/* ============================================================
|
|
EXPORT
|
|
============================================================ */
|
|
module.exports = {
|
|
insertOrUpdateActivity,
|
|
markActivityDeletedHard,
|
|
getActivitiesSince,
|
|
getDeletedHardActivitiesSince
|
|
};
|