<?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_create_analysis_entry (
	trialID text,
	delete_flag text DEFAULT null,
	currentJobID bigint DEFAULT null
)
 RETURNS BIGINT AS $body$
DECLARE
	--Audit variables
	<?php standard_vars() ?>

	Cdelete CURSOR FOR 
		SELECT
			DISTINCT ( baa.bio_assay_analysis_id )
		FROM
			Biomart.bio_assay_analysis baa
		WHERE
			UPPER ( baa.etl_id ) LIKE ( UPPER ( trialID ) || ':%' );
	cDeleteRow record;

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

	-- If this flag is set to 'D', all study data from biomart.bio_assay_analysis and biomart.bio_assay_analysis_data
	IF upper(delete_flag) = 'D' THEN
		PERFORM cz_write_audit(jobId,databaseName,procedureName,'Start Delete bio_assay_analysis_data Loop',0,stepCt,'Done');
		stepCt := stepCt + 1;	

		FOR Cdeleterow IN Cdelete LOOP
			<?php step_begin() ?>
			DELETE
			FROM
				biomart.bio_assay_analysis_data baad
			WHERE
				baad.bio_assay_analysis_id = cDeleteRow.bio_assay_analysis_id;
			<?php step_end("'Delete records for analysis:  ' || cDeleteRow.bio_assay_analysis_id") ?>
		END LOOP;

		<?php step_begin() ?>
		DELETE
		FROM
			Biomart.bio_assay_analysis baa
		WHERE UPPER ( baa.etl_id ) LIKE ( UPPER ( trialID ) || ':%' );
		<?php step_end('Delete existing records from Biomart.bio_assay_analysis') ?>
	END IF;

	<?php step_begin() ?>
	INSERT INTO Biomart.Bio_Assay_Analysis (
		ANALYSIS_NAME,
		Short_Description,
		Long_Description,
		Fold_Change_Cutoff,
		Pvalue_Cutoff,
		lsmean_cutoff,
		Analysis_Method_Cd,
		Bio_Assay_Data_Type,
		Etl_Id,
		Qa_Criteria,
		analysis_create_date,
		analysis_update_date )
	SELECT
		rwg.analysis_id,
		rwg.Short_Desc,
		rwg.Long_Desc,
		rwg.foldchange_cutoff,
		pvalue_cutoff,
		lsmean_cutoff,
		--fold_change, pvalue, lsmean cutoffs
		rwg.Analysis_Type,
		rwg.Data_Type,
		rwg.study_id || 'RWG',
		'(Abs(fold Change) > ' || rwg.foldchange_cutoff || ' OR coalesce(fold_change::text, '') = '')' || ' AND pvalue < ' || pvalue_cutoff || ' AND Max(LSMean) >' || lsmean_cutoff,
		now(),
		now()
	FROM
		tm_lz.rwg_analysis rwg
	WHERE
		UPPER ( rwg.study_id ) = UPPER ( trialID );
	<?php step_end('Insert records into Biomart.Bio_Assay_Analysis') ?>

	/* Update tm_lz.Rwg_Analysis with the newly created bio_assay_analysis_Id */
	<?php step_begin() ?>
	UPDATE tm_lz.Rwg_Analysis rwg
	SET
		rwg.bio_assay_analysis_id = (
			SELECT
				baa.bio_assay_analysis_id
			FROM
				Biomart.Bio_Assay_Analysis baa
			WHERE
				baa.analysis_name = rwg.analysis_id
				AND UPPER ( baa.etl_id ) LIKE UPPER ( trialID || ':%' )
				AND UPPER ( rwg.study_id ) LIKE UPPER ( trialID ) )
	WHERE
		UPPER ( rwg.study_id ) LIKE UPPER ( trialID );
	<?php step_end('Update records in tm_lz.Rwg_Analysis with bio_assay_analysis_id ') ?>

	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;


