<?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_import_from_ext (
  trialID text
 ,currentJobID bigint DEFAULT null
)
 RETURNS BIGINT AS $body$
DECLARE

/*************************************************************************
* Copyright 2008-2012 Janssen Research & Development, LLC.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************/
	<?php standard_vars() ?>
BEGIN
	<?php func_start('RWG_IMPORT_FROM_EXT') ?>

	delete from TM_LZ.Rwg_Analysis where upper(study_id) =upper(trialID);
	GET DIAGNOSTICS rowCt := ROW_COUNT;

	perform cz_write_audit(jobId,databaseName,procedureName,'Delete existing records from TM_LZ.Rwg_Analysis',rowCt,stepCt,'Done');
	stepCt := stepCt + 1;

	delete from TM_LZ.Rwg_Cohorts where upper(study_id) =upper(trialID);
	GET DIAGNOSTICS rowCt := ROW_COUNT;

	perform cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from TM_LZ.Rwg_Cohorts',rowCt,Stepct,'Done');
	stepCt := stepCt + 1;

	delete from TM_LZ.Rwg_Samples where upper(study_id) =upper(trialID);
	perform cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from TM_LZ.Rwg_Samples',rowCt,Stepct,'Done');
	stepCt := stepCt + 1;

	-- not used??
	-- delete from TM_LZ.RWG_BAAD_ID where upper(study_id) =upper(trialID);
	--Cz_Write_Audit(Jobid,Databasename,Procedurename,'Delete existing records from TM_LZ.RWG_BAAD_ID',rowCt,Stepct,'Done');
	--stepCt := stepCt + 1;
	--Insert Analysis
	<?php step_begin() ?>
	INSERT INTO TM_LZ.Rwg_Analysis
	(
		Study_Id,
		Cohorts,
		ANALYSIS_ID,
		pvalue_cutoff,
		foldchange_cutoff ,
		lsmean_cutoff,
		Analysis_Type,
		Data_Type,
		Platform,
		Long_Desc,
		Short_Desc,
		import_date
	)
	SELECT
		Upper(Replace(  Study_Id,'"','')),
		REGEXP_REPLACE(upper(Replace(  Cohorts,'"','')), '\s*', '', 'g'),
		Replace(  ANALYSIS_ID ,'"',''),
		Replace( pvalue_cutoff ,'"','')::double precision,
		Replace(  foldchange_cutoff ,'"','')::double precision,
		Replace( lsmean_cutoff ,'"','')::double precision,
		Replace(  Analysis_Type,'"',''),
		Replace(  Data_Type,'"',''),
		Replace(   Platform,'"',''),
		Replace(  Long_Desc,'"',''),
		Replace(   short_desc,'"',''),
		now()
    FROM  TM_LZ.Rwg_Analysis_Ext
    WHERE upper(study_id) = upper(trialID);
	<?php step_end('Insert records from TM_LZ.Rwg_Analysis_Ext to TM_LZ.Rwg_Analysis') ?>

	--	update bio_assay_analysis_id for any existing analysis_id (20130111 JEA)
	<?php step_begin() ?>
	UPDATE tm_lz.rwg_analysis t
	SET
		bio_assay_analysis_id = (
			SELECT
				b.bio_assay_analysis_id
			FROM
				biomart.bio_assay_analysis b
			WHERE
				b.etl_id = trialID || ':RWG'
				AND b.analysis_name = t.analysis_id )
	WHERE
		t.study_id = trialID
		AND EXISTS (
			SELECT
				1
			FROM
				biomart.bio_assay_analysis x
			WHERE
				x.etl_id = trialID || ':RWG'
				AND t.analysis_id = x.analysis_name );

	<?php step_end('Update bio_assay_analysis_id on existing rwg_analysis records') ?>

    --Insert Cohorts
	<?php step_begin() ?>
	INSERT
	INTO TM_LZ.Rwg_Cohorts
	(
		Study_Id,
		Cohort_Id,
		Cohort_Title, Disease, Long_Desc,
		Organism, Pathology, Sample_Type, Short_Desc, Treatment,IMPORT_DATE
	)
	SELECT
	Upper(Replace(Study_Id,'"','')),
	trim(upper(Replace(Cohort_Id,'"',''))),
	Replace(Cohort_Title, '"',''),
	Replace(Disease, '"',''),
	Replace(Long_Desc, '"',''),
	Replace(Organism, '"',''),
	Replace(Pathology, '"',''),
	Replace(Sample_Type, '"',''),
	Replace(Short_Desc, '"',''),
	Replace(Treatment,'"',''),
	now()
	From  TM_LZ.Rwg_Cohorts_Ext
	where upper(study_id) = upper(trialID);

	<?php step_end('Insert records from TM_LZ.Rwg_Cohorts_Ext to TM_LZ.Rwg_Cohorts') ?>

	--Insert samples
	<?php step_begin() ?>
	INSERT
	INTO TM_LZ.Rwg_Samples(
		study_id, COHORTS, EXPR_ID, IMPORT_DATE
	)
	SELECT
		Upper(Replace(  Study_Id,'"','')),
		trim(upper(Replace(  Cohorts,'"',''))),
		Replace(  Expr_Id, '"',''),
		now()
	FROM  TM_LZ.Rwg_Samples_Ext
	WHERE upper(study_id) = upper(trialID);

	<?php step_end('Insert records from TM_LZ.Rwg_Samples_Ext to TM_LZ.Rwg_Samples') ?>

	---Cleanup OVERALL JOB if this proc is being run standalone
	IF newJobFlag = 1 THEN
		PERFORM cz_end_audit(jobID, 'SUCCESS');
	END IF;

	RETURN 0;
EXCEPTION
	WHEN OTHERS THEN
	<?php error_handle_body(); ?>
END;

$body$
LANGUAGE PLPGSQL;


