Move data from training to live

[DavidRoy]

Request to transfer some counts that were made on training mode to 'live'

240215 - A Rocha Cruzinha I 240222 - A Rocha Cruzinha II 243008 - Mata dos Sete Montes 240301 - Parque Ambiental de Santa Margarida 256572 - ICNF PNA Serra do Louro 272901 - ICNF RNES Gambia

[CrisSevilleja]

Hello again. How is this task going? I have several users that would like to change, training mode surveys into live surveys to appear properly on the website and database.

• User ID 332002, around 605 records are in training mode mentioned on #639
• User ID 297313, 158 surveys (15min counts) in training mode to change into live (Romania)
• User ID 326171, 13 transect surveys in training mode. (needed for Slovenia coordinator)

[JimBacon]

1. Records for user 326171 updated with the following query.

with to_change as (
    select p.id p_id, p.training p_training, s.id s_id, s.training, o.id o_id, o.training
    from cache_samples_functional s
    join cache_occurrences_functional o
        on o.sample_id = s.id
    join cache_samples_functional p
        on p.id = s.parent_sample_id
    where s.created_by_id = 326171
    and s.survey_id = 562
    order by s.parent_sample_id
),
update_o as (
    update occurrences o
    set training = false, updated_on = now(), updated_by_id = 2
    from to_change
    where to_change.o_id = o.id     
),
update_s as (
    update samples s
    set training = false, updated_on = now(), updated_by_id = 2
    from to_change
    where to_change.s_id = s.id     
),
update_s as (
    update samples p
    set training = false, updated_on = now(), updated_by_id = 2
    from to_change
    where to_change.p_id = p.id     
),
update_p_cache as (
    insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
    select distinct on (p_id) 'task_cache_builder_update', 'sample', p_id, null, 100, 2, now()
    from to_change
    order by p_id
),
update_s_cache as (
    insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
    select distinct on (s_id) 'task_cache_builder_update', 'sample', s_id, null, 100, 2, now()
    from to_change
    order by s_id
),
update_o_cache as (
    insert into work_queue(task, entity, record_id, params, cost_estimate, priority, created_on)
    select 'task_cache_builder_update', 'occurrence', o_id, null, 100, 2, now()
    from to_change
    order by o_id
)
select * from work_queue

[JimBacon]

2. Records for user 297313 updated as above but with the following substitution:

with to_change as (
    select p.id p_id, p.training p_training, s.id s_id, s.training, o.id o_id, o.training
    from cache_samples_functional s
    join cache_occurrences_functional o
        on o.sample_id = s.id
    join cache_samples_functional p
        on p.id = s.parent_sample_id
    where s.created_by_id = 297313
    and s.survey_id = 565
    and s.training = true
    order by s.parent_sample_id
)

[JimBacon]

3. Records for user 332002 updated as above but with the following filter.

    where s.created_by_id = 332002
    and s.training = true

[JimBacon]

4. 900 occurrence records for location 240215 were updated as above but with the following filter:

    where p.location_id = 240215
    and s.survey_id = 562
    and o.training = true

[JimBacon]

5. And similar to above, updated
   1. 568 occurrence records for location 240222,
   2. 406 for location 243008
   3. 147 for location 240301
   4. 27 for location 256572
   5. 65 for location 272901

This completes all the actions requested in this issue.