<?php
	$RETURN_METHOD = 'RETURN'; /* RETURN or OUTVAR */
	require __DIR__ . '/../_scripts/macros.php';
?>
-- Generated by Ora2Pg, the Oracle database Schema converter, version 11.4
-- Copyright 2000-2013 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=mydb.mydom.fr;sid=SIDNAME


CREATE OR REPLACE FUNCTION tm_cz.rwg_remove_study (
  trialID text,
  currentJobID bigint DEFAULT null
)
 RETURNS BIGINT AS $body$
DECLARE
--Audit variables
	<?php standard_vars() ?>

	sqlText      varchar(500);
	partExists   boolean;
	V_BIO_EXP_ID bigint;
	partTable    text;

BEGIN
	<?php func_start('RWG_REMOVE_STUDY') ?>

	partTable := 'heat_map_results_' || lower(trialID);

	<?php step_begin() ?>
	SELECT bio_experiment_id INTO V_BIO_EXP_ID
	FROM biomart.bio_experiment
	WHERE UPPER ( accession ) LIKE UPPER ( trialID );
	<?php step_end("'Retrieved exp id ' || V_BIO_EXP_ID || "
		. "' for trial ' || upper(trialID)") ?>

	--check that the experiment id exists
	IF NOT FOUND THEN
		RAISE 'Experiment is missing';
	END IF;

	/************* Delete existing records for study ******************/
	<?php step_begin() ?>
	DELETE
	FROM
		biomart.bio_analysis_attribute_lineage baal
	WHERE
		baal.bio_analysis_attribute_id IN (
			SELECT
				baa.bio_analysis_attribute_id
			FROM
				biomart.bio_analysis_attribute baa
			WHERE
				UPPER ( baa.study_id ) = UPPER ( trialID ) );
	<?php step_end('Delete existing records from Biomart.bio_analysis_attribute_lineage') ?>

	<?php step_begin() ?>
	DELETE
	FROM
		biomart.bio_analysis_attribute baa
	WHERE
		UPPER ( baa.study_id ) = UPPER ( trialID );
	<?php step_end('Delete existing records from Biomart.bio_analysis_attribute') ?>

	<?php step_begin() ?>
	DELETE
	FROM
		biomart.bio_analysis_cohort_xref bacx
	WHERE
		UPPER ( bacx.study_id ) = UPPER ( trialID );
	<?php step_end('Delete existing records from Biomart.bio_analysis_cohort_xref') ?>

	<?php step_begin() ?>
	DELETE
	FROM
		biomart.bio_cohort_exp_xref bcex
	WHERE
		UPPER ( bcex.study_id ) = UPPER ( trialID );
	<?php step_end('Delete existing records from Biomart.bio_cohort_exp_xref') ?>

	<?php step_begin() ?>
	DELETE
	FROM
		biomart.bio_assay_cohort bac
	WHERE
		UPPER ( bac.study_id ) = UPPER ( trialID );
	<?php step_end('Delete existing records from Biomart.bio_assay_cohort') ?>

	<?php step_begin() ?>
	DELETE
	FROM
		biomart.bio_assay_analysis_data baad
	WHERE
		baad.bio_experiment_id = V_BIO_EXP_ID;
	<?php step_end('Delete existing records from Biomart.bio_assay_analysis_data') ?>

	<?php step_begin() ?>
	DELETE
	FROM
		biomart.bio_assay_analysis baa
	WHERE
		baa.bio_assay_analysis_id IN (
			SELECT
				DISTINCT hmr.bio_assay_analysis_id
			FROM
				biomart.heat_map_results hmr
			WHERE
				hmr.trial_name = UPPER ( trialID ) );
	<?php step_end('Delete existing records from Biomart.bio_assay_analysis') ?>

	/***********************************/
	/**** Remove Heat_map_resultes ***/
	/***********************************/
	/* Check if partition exist, truncate if so, create it if not */
	SELECT
		EXISTS (
			SELECT *
			FROM pg_tables
			WHERE
				schemaname = 'biomart'
				AND tablename = partTable )
    INTO partExists;

	IF partExists THEN
		sqlText := 'DROP TABLE biomart.' || partTable || ' CASCADE';
		<?php step_begin() ?>
		EXECUTE(sqlText);
		<?php step_end('Drop partition table of heat_map_results', 0) ?>
	END IF;

	PERFORM cz_write_audit(jobId,databaseName,procedureName,'FUNCTION Complete',0,stepCt,'Done');
	RETURN 0;

	---Cleanup OVERALL JOB if this proc is being run standalone
	IF newJobFlag = 1
		THEN
		PERFORM cz_end_audit (jobID, 'SUCCESS');
	END IF;
EXCEPTION
	WHEN OTHERS THEN
	<?php error_handle_body(); ?>
END;
$body$
LANGUAGE PLPGSQL;
