search

humlab-sead / sead_change_control

Sane SEAD change control using Sqitch.
1 stars 0 forks source link

20191222_DML_DATASET_SUBMISSION_UPDATE: Dataset submission entries updates #49

Closed visead closed 4 years ago

visead commented 5 years ago

On creation of each bugs datasets, tbl_dataset_submissions entries need to be created automatically. Submission type as follows: "submission_type_id";"submission_type";"description" 5;"Compilation into SEAD from another database";"Single dataset from another database submission into SEAD"

roger-mahler commented 5 years ago 
select *
from tbl_dataset_submission_types
BugsData:   submission_type: 5  contact_id: Phil submitted_data: (date_updated from dataset)
MAL_Data:   submission_type: 7 (om Eriks client) contact_id: Phil submitted_data: (date_updated from dataset)

Iso_Data:   submission_type: 12 contact_id: Phil submitted_data: (date_updated from dataset)                       
Dendro:     submission_type: 13 / Compilation into SEAD from another database
Ceramic:    submission_type: 13 / Compilation into SEAD from another database
Pollen:     submission_type: 14 / Compilation into SEAD from another database

-- Ny submission_type: 12, Compilation into SEAD via (articles + Excel) => Excel => XML => CH => SEAD
-- Ny submission_type: 13, Compilation into SEAD via external DB (excel) => Excel => XML => CH => SEAD
-- Ny submission_type: 14, Compilation into SEAD via external TILIA => XML => (Excel) => XML => CH => SEAD
roger-mahler commented 4 years ago

See 20191222_DML_DATASET_SUBMISSION_UPDATE. This code must be verified/approved. The change is moved to SEAD 202002 release.

roger-mahler commented 4 years ago

To clarify: the question marks ???? in the last two insert statements in 20191222_DML_DATASET_SUBMISSION_UPDATE need to be replaced by proper criteria.

visead commented 4 years ago

Changes in bold (at least they were supposed to be!) below. The new submission types need to be entered into tbl_dataset_submission_types

    /* MAL, Eriks */

    insert into tbl_dataset_submissions (dataset_id, submission_type_id, contact_id, date_submitted, notes)

        select d.dataset_id, 7, 1, d.date_updated, **'Compilation into SEAD from primary source using Eriks Erikssons software'**

        from tbl_datasets d

        left join tbl_dataset_submissions s using (dataset_id)

        where TRUE

          and s.dataset_submission_id is null

          and d.master_set_id in (2)

          **and d.method_id<>14**;         /* = Not Pollen */

    /* Pollen */

    insert into tbl_dataset_submissions (dataset_id, submission_type_id, contact_id, date_submitted, notes)

        select d.dataset_id, 15, 1, d.date_updated, **'Single dataset from another database submission into SEAD via Tilia** software'

        from tbl_datasets d

        left join tbl_dataset_submissions s using (dataset_id)

        where TRUE

          and s.dataset_submission_id is null

          and d.master_set_id in (2)

          **and d.method_id=14**; /* = Pollen */