-- USE `ncia`;

CREATE VIEW `all_logins` AS SELECT `login_history`.`login_timestamp` AS `LOGIN_TIMESTAMP`,`login_history`.`ip_address` AS `IP_ADDRESS` FROM `login_history` ORDER BY `login_history`.`login_timestamp` DESC;

CREATE VIEW `anatomic_site` AS SELECT DISTINCT ucase(`general_series`.`body_part_examined`) AS `BODY_PART_EXAMINED` FROM `general_series` WHERE (`general_series`.`visibility` = _LATIN1 '1');

CREATE VIEW `convolution_kernel` AS SELECT DISTINCT `ct_image`.`convolution_kernel` AS `CONVOLUTION_KERNEL` FROM `ct_image` WHERE (`ct_image`.`visibility` = _LATIN1 '1');

CREATE VIEW `image` AS SELECT `gi`.`image_pk_id` AS `IMAGE_PK_ID`,`gi`.`instance_number` AS `INSTANCE_NUMBER`,`gi`.`content_date` AS `CONTENT_DATE`,`gi`.`content_time` AS `CONTENT_TIME`,`gi`.`image_type` AS `IMAGE_TYPE`,`gi`.`acquisition_date` AS `ACQUISITION_DATE`,`gi`.`acquisition_time` AS `ACQUISITION_TIME`,`gi`.`acquisition_number` AS `ACQUISITION_NUMBER`,`gi`.`lossy_image_compression` AS `LOSSY_IMAGE_COMPRESSION`,`gi`.`pixel_spacing` AS `PIXEL_SPACING`,`gi`.`image_orientation_patient` AS `IMAGE_ORIENTATION_PATIENT`,`gi`.`image_position_patient` AS `IMAGE_POSITION_PATIENT`,`gi`.`slice_thickness` AS `SLICE_THICKNESS`,`gi`.`slice_location` AS `SLICE_LOCATION`,`gi`.`i_rows` AS `I_ROWS`,`gi`.`i_columns` AS `I_COLUMNS`,`gi`.`contrast_bolus_agent` AS `CONTRAST_BOLUS_AGENT`,`gi`.`contrast_bolus_route` AS `CONTRAST_BOLUS_ROUTE`,`gi`.`sop_class_uid` AS `SOP_CLASS_UID`,`gi`.`sop_instance_uid` AS `SOP_INSTANCE_UID`,`gi`.`general_series_pk_id` AS `GENERAL_SERIES_PK_ID`,`gi`.`patient_position` AS `PATIENT_POSITION`,`gi`.`source_to_detector_distance` AS `SOURCE_TO_DETECTOR_DISTANCE`,`gi`.`source_subject_distance` AS `SOURCE_SUBJECT_DISTANCE`,`gi`.`focal_spot_size` AS `FOCAL_SPOT_SIZE`,`gi`.`storage_media_file_set_uid` AS `STORAGE_MEDIA_FILE_SET_UID`,`gi`.`mirc_doc_uri` AS `MIRC_DOC_URI`,`gi`.`dicom_file_uri` AS `DICOM_FILE_URI`,`gi`.`acquisition_datetime` AS `ACQUISITION_DATETIME`,`gi`.`image_comments` AS `IMAGE_COMMENTS`,`gi`.`image_receiving_timestamp` AS `IMAGE_RECEIVING_TIMESTAMP`,`gi`.`curation_status` AS `CURATION_STATUS`,`gi`.`curation_timestamp` AS `CURATION_TIMESTAMP`,`gi`.`visibility` AS `VISIBILITY`,`gi`.`annotation` AS `ANNOTATION`,`gi`.`submission_date` AS `SUBMISSION_DATE`,`gi`.`dicom_size` AS `DICOM_SIZE`,`gi`.`image_laterality` AS `IMAGE_LATERALITY`,`gi`.`trial_dp_pk_id` AS `TRIAL_DP_PK_ID`,`gi`.`patient_id` AS `PATIENT_ID`,`gi`.`study_instance_uid` AS `STUDY_INSTANCE_UID`,`gi`.`series_instance_uid` AS `SERIES_INSTANCE_UID`,`gi`.`patient_pk_id` AS `PATIENT_PK_ID`,`gi`.`study_pk_id` AS `STUDY_PK_ID`,`gi`.`project` AS `PROJECT`,`gi`.`version` AS `VERSION`,`gi`.`acquisition_matrix` AS `ACQUISITION_MATRIX`,`gi`.`dx_data_collection_diameter` AS `DX_DATA_COLLECTION_DIAMETER`,`cti`.`kvp` AS `KVP`,`cti`.`scan_options` AS `SCAN_OPTIONS`,`cti`.`data_collection_diameter` AS `DATA_COLLECTION_DIAMETER`,`cti`.`reconstruction_diameter` AS `RECONSTRUCTION_DIAMETER`,`cti`.`gantry_detector_tilt` AS `GANTRY_DETECTOR_TILT`,`cti`.`exposure_time` AS `EXPOSURE_TIME`,`cti`.`x_ray_tube_current` AS `X_RAY_TUBE_CURRENT`,`cti`.`exposure` AS `EXPOSURE`,`cti`.`exposure_in_microas` AS `EXPOSURE_IN_MICROAS`,`cti`.`convolution_kernel` AS `CONVOLUTION_KERNEL`,`cti`.`revolution_time` AS `REVOLUTION_TIME`,`cti`.`single_collimation_width` AS `SINGLE_COLLIMATION_WIDTH`,`cti`.`total_collimation_width` AS `TOTAL_COLLIMATION_WIDTH`,`cti`.`table_speed` AS `TABLE_SPEED`,`cti`.`table_feed_per_rotation` AS `TABLE_FEED_PER_ROTATION`,`cti`.`ct_pitch_factor` AS `CT_PITCH_FACTOR`,`cti`.`anatomic_region_seq` AS `ANATOMIC_REGION_SEQ` FROM (`general_image` `gi` JOIN `ct_image` `cti`) WHERE (`gi`.`image_pk_id` = `cti`.`image_pk_id`);

CREATE VIEW `img_cnt_per_series` AS SELECT `general_image`.`general_series_pk_id` AS `GENERAL_SERIES_PK_ID`,count(0) AS `IMG_COUNT`,sum(`general_image`.`dicom_size`) AS `TOTAL_SIZE` FROM `general_image` WHERE (`general_image`.`visibility` = _LATIN1 '1') GROUP BY `general_image`.`general_series_pk_id`;

CREATE VIEW `img_cnt_per_series_uid` AS SELECT `general_image`.`series_instance_uid` AS `SERIES_INSTANCE_UID`,count(0) AS `IMG_COUNT` FROM `general_image` WHERE (`general_image`.`visibility` = _LATIN1 '1') GROUP BY `general_image`.`series_instance_uid`;

CREATE VIEW `manufacturer` AS SELECT DISTINCT `general_equipment`.`manufacturer` AS `MANUFACTURER` FROM `general_equipment` WHERE `general_equipment`.`general_equipment_pk_id` IN (SELECT `general_series`.`general_equipment_pk_id` AS `GENERAL_EQUIPMENT_PK_ID` FROM `general_series` WHERE (`general_series`.`visibility` = _LATIN1 '1'));

CREATE VIEW `manufacturer_model_name` AS SELECT DISTINCT `general_equipment`.`manufacturer_model_name` AS `MANUFACTURER_MODEL_NAME` FROM `general_equipment` WHERE `general_equipment`.`general_equipment_pk_id` IN (SELECT `general_series`.`general_equipment_pk_id` AS `GENERAL_EQUIPMENT_PK_ID` FROM `general_series` WHERE (`general_series`.`visibility` = _LATIN1 '1'));

CREATE VIEW `manufacturer_model_software` AS SELECT DISTINCT `general_equipment`.`general_equipment_pk_id` AS `ID`,`general_equipment`.`manufacturer` AS `MANUFACTURER`,`general_equipment`.`manufacturer_model_name` AS `MODEL`,`general_equipment`.`software_versions` AS `SOFTWARE` FROM `general_equipment` WHERE `general_equipment`.`general_equipment_pk_id` IN (SELECT `general_series`.`general_equipment_pk_id` AS `GENERAL_EQUIPMENT_PK_ID` FROM `general_series` WHERE (`general_series`.`visibility` = _LATIN1 '1'));

CREATE VIEW `modality` AS SELECT DISTINCT `general_series`.`modality` AS `MODALITY` FROM `general_series` WHERE (`general_series`.`visibility` = _LATIN1 '1');

CREATE VIEW `number_month` AS SELECT `study`.`patient_pk_id` AS `PATIENT_ID`,round(timestampdiff(MONTH,max(`study`.`study_date`),min(`study`.`study_date`)),0) AS `NUMBER_MONTH` FROM `study` WHERE (`study`.`visibility` = _LATIN1 '1') GROUP BY `study`.`patient_pk_id`;

CREATE VIEW `numeric_curation_data` AS SELECT `curation_data`.`curation_data_pk_id` AS `CURATION_DATA_PK_ID`,`curation_data`.`trial_name` AS `TRIAL_NAME`,`curation_data`.`patient_pk_id` AS `PATIENT_PK_ID`,`curation_data`.`study_pk_id` AS `STUDY_PK_ID`,`curation_data`.`general_series_pk_id` AS `GENERAL_SERIES_PK_ID`,`curation_data`.`image_pk_id` AS `IMAGE_PK_ID`,`curation_data`.`annotations_pk_id` AS `ANNOTATIONS_PK_ID`,cast(`curation_data`.`cde_value` AS signed) AS `CDE_VALUE`,`curation_data`.`cde_public_id` AS `CDE_PUBLIC_ID` FROM `curation_data` WHERE (isnull(length(trim(replace(`curation_data`.`cde_value`,_LATIN1 '.0123456789',_LATIN1 ' ')))) AND (substr(`curation_data`.`cde_value`,locate(_LATIN1 '.',`curation_data`.`cde_value`),1) <> _LATIN1 '.'));

CREATE VIEW `project` AS SELECT DISTINCT `dp`.`project` AS `PROJECT` FROM (`trial_data_provenance` `dp` JOIN `patient` `p`) WHERE ((`dp`.`trial_dp_pk_id` = `p`.`trial_dp_pk_id`) AND (`p`.`visibility` = _LATIN1 '1'));

CREATE VIEW `qa_status_summary_tmp` AS SELECT DISTINCT `general_image`.`general_series_pk_id` AS `sid`,`general_image`.`visibility` AS `visibility` FROM `general_image`;

CREATE VIEW `qa_status_summary` AS SELECT `tdp`.`project` AS `PROJECT_NAME`,`p`.`patient_pk_id` AS `PATIENT_PK_ID`,`p`.`patient_id` AS `PATIENT_ID`,`s`.`study_pk_id` AS `STUDY_PK_ID`,`s`.`study_instance_uid` AS `STUDY_UID`,`gs`.`general_series_pk_id` AS `SERIES_PK_ID`,`gs`.`security_group` AS `SERIES_GROUP`,`gs`.`series_instance_uid` AS `SERIES_UID`,`qt`.`visibility` AS `IMAGE_VISIBILITY`,`tdp`.`dp_site_name` AS `DP_SITE_NAME` FROM ((((`trial_data_provenance` `tdp` JOIN `patient` `p`) JOIN `study` `s`) JOIN `general_series` `gs`) JOIN `qa_status_summary_tmp` `qt`) WHERE ((`tdp`.`trial_dp_pk_id` = `p`.`trial_dp_pk_id`) AND (`p`.`patient_pk_id` = `s`.`patient_pk_id`) AND (`s`.`study_pk_id` = `gs`.`study_pk_id`) AND (`qt`.`sid` = `gs`.`general_series_pk_id`));

CREATE VIEW `quarantine` AS (SELECT `gs`.`general_series_pk_id` AS `GS_SERIES_PK_ID`,`gs`.`series_instance_uid` AS `SERIES_INSTANCE_UID`,`s`.`study_instance_uid` AS `STUDY_INSTANCE_UID`,`tdp`.`project` AS `PROJECT`,`tdp`.`dp_site_name` AS `DP_SITE_NAME` FROM (((`patient` `p` JOIN `study` `s`) JOIN `general_series` `gs`) JOIN `trial_data_provenance` `tdp`) WHERE ((`p`.`trial_dp_pk_id` = `tdp`.`trial_dp_pk_id`) AND (`s`.`patient_pk_id` = `p`.`patient_pk_id`) AND (`s`.`study_pk_id` = `gs`.`study_pk_id`)));

CREATE VIEW `saved_query_last_exec` AS SELECT `qh`.`saved_query_pk_id` AS `SAVED_QUERY_PK_ID`,max(`qh`.`query_execute_timestamp`) AS `LAST_EXECUTE_DATE` FROM (`saved_query` `sq` JOIN `query_history` `qh`) WHERE (`qh`.`saved_query_pk_id` = `sq`.`saved_query_pk_id`) GROUP BY `qh`.`saved_query_pk_id`;

CREATE VIEW `series_total_ann_file_size` AS SELECT `a`.`general_series_pk_id` AS `SERIES_PK_ID`,sum(`a`.`file_size`) AS `TOTAL_FILE_SIZE` FROM `annotation` `a` GROUP BY `a`.`general_series_pk_id` UNION SELECT `general_series`.`general_series_pk_id` AS `general_series_pk_id`,0 AS `0` FROM `general_series` WHERE ((`general_series`.`annotations_flag` = 0) OR isnull(`general_series`.`annotations_flag`));

CREATE VIEW `software_versions` AS SELECT DISTINCT `general_equipment`.`software_versions` AS `SOFTWARE_VERSIONS` FROM `general_equipment` WHERE `general_equipment`.`general_equipment_pk_id` IN (SELECT `general_series`.`general_equipment_pk_id` AS `GENERAL_EQUIPMENT_PK_ID` FROM `general_series` WHERE ((`general_series`.`visibility` = _LATIN1 '1') AND (`general_equipment`.`software_versions` IS NOT NULL)));

CREATE VIEW `study_series_number` AS SELECT `g`.`patient_pk_id` AS `PATIENT_PK_ID`,`p`.`patient_id` AS `PATIENT_ID`,`dp`.`project` AS `PROJECT`,count(DISTINCT `g`.`study_pk_id`) AS `STUDY_NUMBER`,count(DISTINCT `g`.`general_series_pk_id`) AS `SERIES_NUMBER` FROM ((`general_series` `g` JOIN `patient` `p`) JOIN `trial_data_provenance` `dp`) WHERE ((`g`.`visibility` = _LATIN1 '1') AND (`g`.`patient_pk_id` = `p`.`patient_pk_id`) AND (`p`.`trial_dp_pk_id` = `dp`.`trial_dp_pk_id`)) GROUP BY `g`.`patient_pk_id`,`p`.`patient_id`,`dp`.`project`;

CREATE VIEW `submission_count_by_month` AS SELECT `tdp`.`project` AS `PROJECT`,`tdp`.`dp_site_name` AS `DP_SITE_NAME`,date_format(`gi`.`submission_date`,_UTF8 '%m') AS `MONTH`,date_format(`gi`.`submission_date`,_UTF8 '%Y') AS `YEAR`,count(0) AS `COUNT` FROM (`general_image` `gi` JOIN `trial_data_provenance` `tdp`) WHERE (`gi`.`trial_dp_pk_id` = `tdp`.`trial_dp_pk_id`) GROUP BY `tdp`.`project`,`tdp`.`dp_site_name`,date_format(`gi`.`submission_date`,_UTF8 '%m'),date_format(`gi`.`submission_date`,_UTF8 '%Y') ORDER BY `tdp`.`project`,`tdp`.`dp_site_name`,date_format(`gi`.`submission_date`,_UTF8 '%Y'),date_format(`gi`.`submission_date`,_UTF8 '%m');

CREATE VIEW `submitted_annotation` AS (SELECT `tdp`.`project` AS `PROJECT`,`tdp`.`dp_site_name` AS `DP_SITE_NAME`,`a`.`submission_date` AS `SUBMISSION_DATE`,`tdp`.`trial_dp_pk_id` AS `TRIAL_DP_PK_ID`,`p`.`patient_pk_id` AS `PATIENT_PK_ID`,`s`.`study_pk_id` AS `STUDY_PK_ID`,`gs`.`general_series_pk_id` AS `SERIES_PK_ID`,count(DISTINCT `a`.`annotations_pk_id`) AS `NUMBER_OF_ANNOTATION` FROM ((((`trial_data_provenance` `tdp` JOIN `annotation` `a`) JOIN `general_series` `gs`) JOIN `study` `s`) JOIN `patient` `p`) WHERE ((`a`.`general_series_pk_id` = `gs`.`general_series_pk_id`) AND (`tdp`.`trial_dp_pk_id` = `p`.`trial_dp_pk_id`) AND (`gs`.`study_pk_id` = `s`.`study_pk_id`) AND (`s`.`patient_pk_id` = `p`.`patient_pk_id`)) GROUP BY `tdp`.`project`,`tdp`.`dp_site_name`,`a`.`submission_date`,`tdp`.`trial_dp_pk_id`,`p`.`patient_pk_id`,`s`.`study_pk_id`,`gs`.`general_series_pk_id`);

CREATE VIEW `submitted_images` AS (SELECT `tdp`.`project` AS `PROJECT`,`tdp`.`dp_site_name` AS `DP_SITE_NAME`,`gi`.`submission_date` AS `SUBMISSION_DATE`,`tdp`.`trial_dp_pk_id` AS `TRIAL_DP_PK_ID`,`gi`.`patient_pk_id` AS `PATIENT_PK_ID`,`gi`.`study_pk_id` AS `STUDY_PK_ID`,`gi`.`general_series_pk_id` AS `SERIES_PK_ID`,`gi`.`visibility` AS `VISIBILITY`,count(`gi`.`image_pk_id`) AS `NUMBER_OF_IMAGES` FROM (`trial_data_provenance` `tdp` JOIN `general_image` `gi`) WHERE (`gi`.`trial_dp_pk_id` = `tdp`.`trial_dp_pk_id`) GROUP BY `tdp`.`project`,`tdp`.`dp_site_name`,`gi`.`submission_date`,`tdp`.`trial_dp_pk_id`,`gi`.`patient_pk_id`,`gi`.`study_pk_id`,`gi`.`general_series_pk_id`,`gi`.`visibility`);

CREATE VIEW `submitted_non_visible` AS (SELECT `tdp`.`project` AS `PROJECT`,`tdp`.`dp_site_name` AS `DP_SITE_NAME`,`gi`.`submission_date` AS `SUBMISSION_DATE`,`tdp`.`trial_dp_pk_id` AS `TRIAL_DP_PK_ID`,`gi`.`patient_pk_id` AS `PATIENT_PK_ID`,`gi`.`study_pk_id` AS `STUDY_PK_ID`,`gi`.`general_series_pk_id` AS `SERIES_PK_ID`,count(DISTINCT `gi`.`image_pk_id`) AS `NUMBER_OF_IMAGES` FROM (`trial_data_provenance` `tdp` JOIN `general_image` `gi`) WHERE ((`gi`.`trial_dp_pk_id` = `tdp`.`trial_dp_pk_id`) AND isnull(`gi`.`visibility`)) GROUP BY `tdp`.`project`,`tdp`.`dp_site_name`,`gi`.`submission_date`,`tdp`.`trial_dp_pk_id`,`gi`.`patient_pk_id`,`gi`.`study_pk_id`,`gi`.`general_series_pk_id`);

CREATE VIEW `submitted_visible` AS (SELECT `tdp`.`project` AS `PROJECT`,`tdp`.`dp_site_name` AS `DP_SITE_NAME`,`gi`.`submission_date` AS `SUBMISSION_DATE`,`tdp`.`trial_dp_pk_id` AS `TRIAL_DP_PK_ID`,`gi`.`patient_pk_id` AS `PATIENT_PK_ID`,`gi`.`study_pk_id` AS `STUDY_PK_ID`,`gi`.`general_series_pk_id` AS `SERIES_PK_ID`,count(`gi`.`image_pk_id`) AS `NUMBER_OF_IMAGES` FROM (`trial_data_provenance` `tdp` JOIN `general_image` `gi`) WHERE ((`gi`.`trial_dp_pk_id` = `tdp`.`trial_dp_pk_id`) AND (`gi`.`visibility` = _LATIN1 '1')) GROUP BY `tdp`.`project`,`tdp`.`dp_site_name`,`gi`.`submission_date`,`tdp`.`trial_dp_pk_id`,`gi`.`patient_pk_id`,`gi`.`study_pk_id`,`gi`.`general_series_pk_id`);

COMMIT;
