imagingsignature/loaddata.sql at master · EGates1/imagingsignature · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- mysql < loaddata.sql

use RandomForestHCCResponse;
DROP TABLE IF EXISTS RandomForestHCCResponse.crcmutations;
CREATE TABLE RandomForestHCCResponse.crcmutations(
  MRN	             int not null,
  MutationalStatus     VARCHAR(32)     not null,
  MutationalStatusAPC  VARCHAR(32)     not null,
  MutationalStatusKras VARCHAR(32)     not null,
  MutationalStatusp53  VARCHAR(32)     not null,
  ImageDate	             DATE         NULL COMMENT 'Study Date',
  StudyUID            VARCHAR(256)    not NULL  COMMENT 'study UID'              ,
  SeriesUIDVen        VARCHAR(256)         NULL  COMMENT 'series UID'             ,
  SeriesACQVen        VARCHAR(256)         NULL  COMMENT 'series acquisition time',
  NotRun                    int            NULL  COMMENT 'error checking ',
  PRIMARY KEY (StudyUID)
);

-- ignore duplicates
insert ignore into RandomForestHCCResponse.crcmutations( MRN ,MutationalStatus ,MutationalStatusAPC , MutationalStatusKras, MutationalStatusp53 , ImageDate	 ,StudyUID      ,SeriesUIDVen ,SeriesACQVen)
 SELECT JSON_UNQUOTE(eu.data->"$.""MRN""") MRN,
         JSON_UNQUOTE(data->"$.""Triple status""") MutationalStatus    ,
         JSON_UNQUOTE(data->"$.""Triple status""") MutationalStatusAPC ,
         JSON_UNQUOTE(data->"$.""Triple status""") MutationalStatusKras,
         JSON_UNQUOTE(data->"$.""Triple status""") MutationalStatusp53 ,
         JSON_UNQUOTE(eu.data->"$.""Image Date""") ImageDate,
         JSON_UNQUOTE(eu.data->"$.""Study UID""") StudyUID,
         replace(substring_index( json_unquote(eu.data->'$."VEN Series UID"'), ':', 1),'{','') SeriesUIDVen,
         replace(substring_index( json_unquote(eu.data->'$."VEN Series UID"'), ':',-1),'}','') SeriesACQVen
         FROM ClinicalStudies.excelUpload eu where eu.uploadID = 84  and JSON_UNQUOTE(eu.data->"$.""Study UID""") is not null;

-- fixme need single table
update RandomForestHCCResponse.crcmutations rf
  join ClinicalStudies.excelUpload eu   on (eu.uploadID=87 and rf.MRN = JSON_UNQUOTE(eu.data->"$.""MRN""") )
   SET rf.MutationalStatusAPC=JSON_UNQUOTE(eu.data->"$.""APC""")   ,
       rf.MutationalStatusKras=JSON_UNQUOTE(eu.data->"$.""Kras""") ,
       rf.MutationalStatusp53 =JSON_UNQUOTE(eu.data->"$.""p53""") ,
       rf.NotRun  = JSON_UNQUOTE(eu.data->"$.""Not Run""") ;


-- error check duplicates
insert into Metadata.Singular(id)
(select si.id from Metadata.Singular si join(
   select * from RandomForestHCCResponse.crcmutations cm where cm.StudyUID=cm.SeriesUIDVen
                                            ) b );

insert into Metadata.Singular(id)
(select si.id from Metadata.Singular si join(
   select count(cm.mrn) numgrp from RandomForestHCCResponse.crcmutations cm group by cm.studyuid
                                            ) b  where b.numgrp=2 );


-- verify 14 WT  did not get deleted on insert
insert into Metadata.Singular(id)
(select si.id from Metadata.Singular si join(
   select count( rf.StudyUID) numtruth from RandomForestHCCResponse.crcmutations rf where rf.MutationalStatus = 'WT'
                                   ) b on b.numtruth !=14 );

-- verify 24 mut  did not get deleted on insert
insert into Metadata.Singular(id)
(select si.id from Metadata.Singular si join(
   select count( rf.StudyUID) numtruth from RandomForestHCCResponse.crcmutations rf where rf.MutationalStatus = 'mut'
                                   ) b on b.numtruth !=24 );

-- use dicomheaders as dflt study date for each
update RandomForestHCCResponse.crcmutations rf
  join DICOMHeaders.studies  sd     on sd.StudyInstanceUID=rf.StudyUID
   SET rf.ImageDate=coalesce(sd.StudyDate,rf.ImageDate);


DROP PROCEDURE IF EXISTS RandomForestHCCResponse.CRCMutDependencies;
DELIMITER //
CREATE PROCEDURE RandomForestHCCResponse.CRCMutDependencies
()
BEGIN
  SET SESSION group_concat_max_len = 10000000;

    select concat("NUMCRCMET =",count(rf.studyUID)) from RandomForestHCCResponse.crcmutations rf;
    select concat("CRCMETTRAIN =", group_concat(
           CONCAT_WS('/',rf.mrn,  REPLACE(rf.ImageDate, '-', ''), rf.StudyUID  )
                                separator ' ') )
    from RandomForestHCCResponse.crcmutations rf
    where rf.SeriesACQVen is not null and (rf.MutationalStatus = 'WT' or rf.MutationalStatus = 'mut');
    select concat("CRCMETTEST =", group_concat(
           CONCAT_WS('/',rf.mrn,  REPLACE(rf.ImageDate, '-', ''), rf.StudyUID  )
                                separator ' ') )
    from RandomForestHCCResponse.crcmutations rf
    where rf.SeriesACQVen is not null and (rf.MutationalStatus != 'WT' and rf.MutationalStatus != 'mut');
    select concat("CRCMETNOTRUN =", group_concat(
           CONCAT_WS('/',rf.mrn,  REPLACE(rf.ImageDate, '-', ''), rf.StudyUID  )
                                separator ' ') )
    from RandomForestHCCResponse.crcmutations rf
    where rf.NotRun =1;

    select concat("NUMRAWVEN =",count(rf.SeriesACQVen)) from RandomForestHCCResponse.crcmutations rf where rf.SeriesACQVen is not null and rf.ImageDate is not null ;
    select concat("RAWVEN  =", group_concat( distinct
           CONCAT_WS('/','ImageDatabase', rf.mrn,  REPLACE(rf.ImageDate, '-', ''), rf.StudyUID, 'Ven.raw.nii.gz ' )
                              separator ' ') )
    from RandomForestHCCResponse.crcmutations rf
    where rf.SeriesACQVen is not null and rf.ImageDate is not null ;

    -- convert to nifti
    select CONCAT('ImageDatabase/', a.mrn, '/', REPLACE(a.StudyDate, '-', ''),'/', a.StudyUID, '/', a.Phase ,'.raw.nii.gz: ImageDatabase/', a.mrn, '/', REPLACE(a.StudyDate, '-', ''),'/', a.StudyUID, '/',a.SeriesUID, '/raw.xfer \n\tif [ ! -f ImageDatabase/', a.mrn, '/', REPLACE(a.StudyDate, '-', ''),'/', a.StudyUID, '/',a.SeriesUID,'/',a.SeriesACQ,'.nii.gz   ] ; then mkdir -p ImageDatabase/', a.mrn, '/', REPLACE(a.StudyDate, '-', ''),'/', a.StudyUID, '/',a.SeriesUID,' ;$(DCMNIFTISPLIT) $(subst ImageDatabase,/FUS4/IPVL_research,$(<D)) $(@D)  \'0008|0032\' ; else echo skipping network filesystem; fi\n\tln -snf ./',a.SeriesUID,'/',a.SeriesACQ,'.nii.gz $@; touch -h -r $(@D)/',a.SeriesUID,'/',a.SeriesACQ,'.nii.gz  $@;\n\tln -snf ./',a.SeriesUID,'/ $(subst .nii.gz,.dir,$@)')
    from (
          select rf.mrn,'Ven' as Phase,rf.Imagedate as StudyDate,rf.StudyUID as StudyUID,rf.SeriesUIDVen as SeriesUID,rf.SeriesACQVen as SeriesACQ from RandomForestHCCResponse.crcmutations rf
         ) a
    where a.SeriesACQ is not null
    group by a.SeriesUID, a.SeriesACQ;
END //
DELIMITER ;

-- show create procedure RandomForestHCCResponse.CRCMutDependencies;
-- mysql  -sNre  "call RandomForestHCCResponse.CRCMutDependencies();"| sed "s/NULL//g" >  datalocation/dependencies

DROP PROCEDURE IF EXISTS RandomForestHCCResponse.CRCPyRadMatrix ;
DELIMITER //
CREATE PROCEDURE RandomForestHCCResponse.CRCPyRadMatrix
(  )
BEGIN
   -- select  a.mrn, a.TIMEID, sd.studyInstanceUID, lk.location,
  select cm.mrn,cm.imagedate ,cm.MutationalStatus, cm.MutationalStatusAPC, cm.MutationalStatusKras, cm.MutationalStatusp53,
         concat_WS('/','/rsrch1/ip/dtfuentes/github/imagingsignature/ImageDatabase',cm.mrn,REPLACE(cm.ImageDate, '-', ''),cm.StudyUID,'/Ven.raw.nii.gz') Image ,
         concat_WS('/','/rsrch1/ip/dtfuentes/github/imagingsignature/ImageDatabase',cm.mrn,REPLACE(cm.ImageDate, '-', ''),cm.StudyUID,'/Cascade/LABELSNN.nii.gz') Mask,
         lk.labelID Label
   from  RandomForestHCCResponse.crcmutations   cm
   join  RandomForestHCCResponse.liverLabelKey  lk on lk.labelID in (2);
END //
DELIMITER ;
-- mysql  -re "call RandomForestHCCResponse.CRCPyRadMatrix ();"     | sed "s/\t/,/g;s/NULL//g" > datalocation/pyradiomics.csv