<?xml version="1.0" encoding="utf-8"?>
<!--

Copyright (c) 2002,2003 The Regenstrief Institute.  All rights reserved.

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.

Written by Gunther Schadow.

$Id: spextract-dataload.xsl,v 1.3 2005/02/18 04:16:31 gschadow Exp $

-->
<xsl:transform version="1.0"
   xmlns:saxon="http://saxon.sf.net/"
   xmlns:xwsf="java:net.sf.xwsf.saxon.Functions"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   xmlns:ex="http://xwsf.sf.net/net.sf.xwsf.saxon.ExceptionElementFactory"
   xmlns:exd="http://xwsf.sf.net/exception"
   xmlns:rs="java:java.sql.ResultSet"
   xmlns:sys="java:java.lang.System"
   xmlns:gut="http://aurora.regenstrief.org/XML/GenericUpTranslator"
   extension-element-prefixes="saxon ex sys"
   exclude-result-prefixes="saxon ex rs exd sys gut xwsf">
<!-- spextract-dataload

  Load the data from the spextract coding into the database. Populate
  the tables SPECIMEN, CLINICAL_VARIABLE, MEDICAL_ORDER

  SPECIMEN_ID (generated from sequence)
  INSTITUTION_ID (same as for related report)
  MEDICAL_ORDER_ID (indirectly refers to the text report)
  PATIENT_ID (same as for related report)
  TISSUE_TYPE_TEXT (text fragment that was basis for coding)
  TISSUE_TYPE_CODE (UMLS CUI)
  TISSUE_TYPE_SYS_ID (code system for "UMLS CUI")
  COLLECTION_METHOD_TEXT (text fragment that was basis for coding)
  COLLECTION_METHOD_CODE (UMLS CUI)
  COLLECTION_METHOD_SYS_ID (code system for "UMLS CUI")
  BODY_SITE_MODIFIER_TEXT (text fragment that was basis for coding)
  BODY_SITE_MODIFIER_CODE (UMLS CUI)
  BODY_SITE_MODIFIER_SYS_ID (code system for "UMLS CUI")

  SERVICE_CODE for tissue diagnosis is 300016

-->

  <xsl:import href="exception.xsl"/>
  <xsl:import href="db-common.xsl"/>
 
  <xsl:output method="xml" 
	      indent="yes" 
	      encoding="UTF-8"
	      media-type="text/xml"/>
  <xsl:strip-space elements="*"/>

  <xsl:param name="debug" select="0"/>
  <xsl:param name="schema" select="'RMRS.'"/>
  <xsl:param name="check" select="/*/@check"/>
  <xsl:param name="force" select="/*/@force"/>
  <xsl:param name="immediate" select="/.."/>

  <xsl:param name="insertSpecimenSQL">
     BEGIN
       INSERT INTO <xsl:value-of select="$schema"/>SPECIMEN (
           SPECIMEN_ID,

	   INSTITUTION_ID,
	   PATIENT_ID,
	   MEDICAL_ORDER_ID,	   

	   COLLECTION_TIME,

	   TISSUE_TYPE_CODE,
	   TISSUE_TYPE_SYS_ID,
	   TISSUE_TYPE_TEXT,
	   COLLECTION_METHOD_CODE,
	   COLLECTION_METHOD_SYS_ID,
	   COLLECTION_METHOD_TEXT,
	   BODY_SITE_MODIFIER_CODE,
	   BODY_SITE_MODIFIER_SYS_ID,
	   BODY_SITE_MODIFIER_TEXT
         ) VALUES (
           <xsl:value-of select="$schema"/>SPECIMEN_SEQUENCE.NEXTVAL,
	   ?, ?, ?,
	   ?, 
	   ?, CASE ? WHEN NULL THEN NULL ELSE 1036 END, SUBSTR(?,1,100),
	   ?, CASE ? WHEN NULL THEN NULL ELSE 1036 END, SUBSTR(?,1,100),
	   ?, CASE ? WHEN NULL THEN NULL ELSE 1036 END, SUBSTR(?,1,100)
         )
       RETURNING SPECIMEN_ID INTO ?;
     END;
  </xsl:param>

  <xsl:param name="insertClinicalVariableSQL">
     BEGIN
       INSERT INTO <xsl:value-of select="$schema"/>CLINICAL_VARIABLE (
           CLINICAL_VARIABLE_ID,

	   SERVICE_CODE,
	   SERVICE_SYS_ID,
	   TOP_PARENT_SERVICE_CODE,
	   TOP_PARENT_SERVICE_SYS_ID,
	   STATUS_CODE,
	   ORIGINATION_CODE,
	   VALUE_TYPE,
	   VALUE_IF_TYPE_IS_CODED_SYS_ID,
	   DATA_ARRIVAL_TIME,
	   PRODUCER_APPLICATION_ID,

	   INSTITUTION_ID,
	   PATIENT_ID,

	   TOP_PARENT_FILLER_ORDER_ID,
	   FILLER_ORDER_ID,
           SPECIMEN_ID,

	   PHYSIOLOGIC_TIME,

	   VALUE_IF_TYPE_IS_CODED_CODE,
	   VALUE_TEXT_FOR_DISPLAY,
	   DELIV_VALUE_TEXT,
	   SISTER_SEQUENCE_NUMBER,
	   DEWEY_DECIMAL_TREE_SORT_ORDER
         ) VALUES (
           <xsl:value-of select="$schema"/>CLINICAL_VARIABLE_SEQUENCE.NEXTVAL,
	   '30016', 1, '30016', 1, 'F', 'S', 'C', 1036, SYSDATE, 603,
	   ?, ?, 
	   ?, ?, ?,
	   ?, 
	   ?, ?, ?, ?, ?
         )
       RETURNING CLINICAL_VARIABLE_ID INTO ?;
     END;
  </xsl:param>

  <xsl:param name="insertOrderSQL">
     BEGIN
       INSERT INTO <xsl:value-of select="$schema"/>MEDICAL_ORDER (
           MEDICAL_ORDER_ID,

	   MOOD_CODE,
	   MOOD_SYS_ID,
	   SERVICE_SYS_ID,
	   RESULT_STATUS_CODE,
	   RESULT_STATUS_SYS_ID,
	   DATA_ARRIVAL_TIME,
	   PRODUCER_APPLICATION_ID,

	   INSTITUTION_ID,
	   PATIENT_ID,
	   ENCOUNTER_ID,
	   PARENT_ORDER_ID,

	   SERVICE_CODE,
	   PHYSIOLOGIC_TIME,
	   PRODUCTION_TIME

         ) VALUES (
           <xsl:value-of select="$schema"/>ORDER_SEQUENCE.NEXTVAL,
	   'FILORD', 19, 1, 'F', 300, SYSDATE, 603,
	   ?, ?, ?, ?,
	   ?, ?, ?
         )
       RETURNING MEDICAL_ORDER_ID INTO ?;
     END;
  </xsl:param>

  <xsl:param name="reportSQL">
     SELECT --+FIRST_ROWS
       MULTIMEDIA_CONTENT_ID,
       RESULT_XML,
       END_TIME,
       MEDICAL_ORDER_ID,
       PATIENT_ID,
       INSTITUTION_ID,
       SERVICE_CODE,
       SERVICE_SYS_ID,    
       PHYSIOLOGIC_TIME,
       ENCOUNTER_ID,
       PRODUCTION_TIME
     FROM SPEXTRACT_WORK
       INNER JOIN <xsl:value-of select="$schema"/>MULTIMEDIA_CONTENT
         USING(INSTITUTION_ID, MULTIMEDIA_CONTENT_ID)
       INNER JOIN (
         SELECT MEDICAL_ORDER_ID,
                PATIENT_ID,
                SERVICE_CODE,
                SERVICE_SYS_ID,
                PHYSIOLOGIC_TIME,
                ENCOUNTER_ID,
                PRODUCTION_TIME
           FROM <xsl:value-of select="$schema"/>MEDICAL_ORDER
       ) USING(MEDICAL_ORDER_ID)
     WHERE STATUS='FINISHED'
       AND PHYSIOLOGIC_TIME IS NOT NULL
  </xsl:param>

  <xsl:param name="insertUMLSConceptSQL">
    DECLARE
      one NUMBER;
    BEGIN
      SELECT 1 INTO one
        FROM <xsl:value-of
           select="$schema"/>CONCEPT 
        WHERE CODE=? AND SYS_ID=1036;
    EXCEPTION 
      WHEN NO_DATA_FOUND THEN
        INSERT INTO <xsl:value-of select="$schema"/>CONCEPT (
          SYS_ID,
          CODE,
          NAME,
          CREATION_TIME
        ) VALUES ( 1036, ?, SUBSTR(?,1,100), SYSDATE );
    END;
  </xsl:param>

  <xsl:param name="updateSpextractWorkSQL">
     UPDATE SPEXTRACT_WORK       
       SET RESULT_ORDER_ID = ?, 
           RESULT_PROCESSED_TIME = SYSDATE
       WHERE MULTIMEDIA_CONTENT_ID = ?
  </xsl:param>

  <xsl:param name="insertOrderStmt"
       xmlns:cn="java:java.sql.Connection"
       select="cn:prepareCall($connection, $insertOrderSQL)"/>

  <xsl:param name="insertSpecimenStmt"
       xmlns:cn="java:java.sql.Connection"
       select="cn:prepareCall($connection, $insertSpecimenSQL)"/>

  <xsl:param name="insertClinicalVariableStmt"
       xmlns:cn="java:java.sql.Connection"
       select="cn:prepareCall($connection, $insertClinicalVariableSQL)"/>

  <xsl:param name="insertUMLSConceptStmt"
       xmlns:cn="java:java.sql.Connection"
       select="cn:prepareCall($connection, $insertUMLSConceptSQL)"/>

  <xsl:param name="updateSpextractWorkStmt"
       xmlns:cn="java:java.sql.Connection"
       select="cn:prepareStatement($connection, $updateSpextractWorkSQL)"/>

  <xsl:template match="/">
    <dataload-transcript>
      <ex:try atomic="no">
	<xsl:choose>
	  <xsl:when test="$immediate">
	    <xsl:message><IMMEDIATE id="{$immediate}"/></xsl:message>
	    <xsl:apply-templates mode="sqlselect" select=".">
	      <xsl:with-param name="whereClause"
		select="concat(' WHERE MULTIMEDIA_CONTENT_ID = ',$immediate)"/>
	    </xsl:apply-templates>
	  </xsl:when>
	  <xsl:otherwise>
	    <xsl:apply-templates select="node()"/>
	  </xsl:otherwise>
	</xsl:choose>

	<ex:catch exception="java.lang.Throwable">
	  <xsl:message terminate="yes">
	    <xsl:call-template name="exd:dump">
	      <xsl:with-param name="name" select="'ERROR'"/>
	    </xsl:call-template>
	  </xsl:message>
	</ex:catch>
        <ex:finally>
          <xsl:sequence xmlns:c="java:java.sql.Connection"
                        select="c:close($connection)"/>
        </ex:finally>
      </ex:try>  
    </dataload-transcript>
  </xsl:template>

  <xsl:template match="@*|node()">
    <xsl:apply-templates select="@*|node()"/>
  </xsl:template>

  <xsl:template match="report-batch[@url]">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="report">
    <xsl:param name="whereClause">
      <xsl:if test="not($force)">
        AND RESULT_ORDER_ID IS NULL
        AND RESULT_PROCESSED_TIME IS NULL
      </xsl:if>
      <xsl:apply-templates mode="makeWhereClause" select="@*|node()"/>
    </xsl:param>
    <xsl:choose>
      <xsl:when test="$whereClause">
        <xsl:apply-templates mode="sqlselect" select=".">
          <xsl:with-param name="whereClause" select="$whereClause"/>
        </xsl:apply-templates>
      </xsl:when>
      <xsl:otherwise>
        <xsl:apply-templates mode="sqlselect" select="."/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <xsl:variable name="calendar" xmlns:cal="java:java.util.Calendar" select="cal:getInstance()"/>

  <xsl:template mode="sqlselect" match="report">
    <xsl:param name="schema" select="ancestor-or-self::*/@schema[1]"/>
    <xsl:param name="whereClause" select="''"/>
    <xsl:if test="$debug > 1">
      <xsl:message>
	<reportSQL>
          <xsl:value-of select="concat($reportSQL,$whereClause)"/>
	</reportSQL>
      </xsl:message>
    </xsl:if>
    <xsl:variable name="rset"
       xmlns:cn="java:java.sql.Connection"
       xmlns:st="java:java.sql.Statement"
       select="st:executeQuery(cn:createStatement($connection), 
                     concat($reportSQL,' ',$whereClause))"/>

    <saxon:while test="rs:next($rset)">
      <xsl:variable name="id" 
	  select="rs:getLong($rset,'MULTIMEDIA_CONTENT_ID')"/>
      <xsl:message>
        <ID id="{$id}"/>
      </xsl:message>
      <ex:try atomic="yes">
	  <xsl:variable name="textString">
	    <xsl:choose>
	      <xsl:when test="ancestor-or-self::report-batch/@use-clob='no'">
		<xsl:value-of 
		   select="rs:getString($rset,'RESULT_XML')"/>
	      </xsl:when>
	      <xsl:otherwise>
		<xsl:variable name="textClob"
		   select="rs:getClob($rset,'RESULT_XML')"/>       
		<xsl:value-of xmlns:clob="java:java.sql.Clob"
		   select="clob:getSubString($textClob,1,clob:length($textClob))"/>
	      </xsl:otherwise>
	    </xsl:choose>
	  </xsl:variable>
	  <xsl:variable name="text" select="saxon:parse($textString)"/>
    <xsl:if test="$debug > 3">
      <xsl:message>
	<text>
          <xsl:copy-of select="$text"/>
	</text>
      </xsl:message>
    </xsl:if>

	  <xsl:for-each select="$text/spextract">
	   <xsl:apply-templates mode="upload" select=".">
 	    <xsl:with-param name="insertOrderStmt"
              xmlns:s="java:java.sql.CallableStatement"
              xmlns:t="java:java.sql.Types"
              xmlns:w="java:org.regenstrief.saxon.Workaround"
	      select="xwsf:progn(w:callableStatement_setLong($insertOrderStmt,1,rs:getLong($rset, 'INSTITUTION_ID')),
	               w:callableStatement_setLong($insertOrderStmt,2,rs:getLong($rset, 'PATIENT_ID')),
		       w:callableStatement_setLong($insertOrderStmt,3,rs:getLong($rset, 'ENCOUNTER_ID')),
		       if(rs:wasNull($rset))
                         then s:setNull($insertOrderStmt,3,t:INTEGER())
			 else (),
		       w:callableStatement_setLong($insertOrderStmt,4,rs:getLong($rset, 'MEDICAL_ORDER_ID')),
		       w:callableStatement_setString($insertOrderStmt,5,rs:getString($rset, 'SERVICE_CODE')),
		       w:callableStatement_setTimestamp($insertOrderStmt,6,rs:getTimestamp($rset, 'PHYSIOLOGIC_TIME')),
		       w:callableStatement_setTimestamp($insertOrderStmt,7,rs:getTimestamp($rset, 'PRODUCTION_TIME')),
	               s:registerOutParameter($insertOrderStmt,8,t:INTEGER()),
		       $insertOrderStmt)"/>
	    <xsl:with-param name="insertSpecimenStmt"
              xmlns:s="java:java.sql.CallableStatement"
              xmlns:t="java:java.sql.Types"
              xmlns:w="java:org.regenstrief.saxon.Workaround"
	      select="xwsf:progn(w:callableStatement_setLong($insertSpecimenStmt,1,rs:getLong($rset, 'INSTITUTION_ID')),
	               w:callableStatement_setLong($insertSpecimenStmt,2,rs:getLong($rset, 'PATIENT_ID')),
		       w:callableStatement_setTimestamp($insertSpecimenStmt,4,rs:getTimestamp($rset, 'PHYSIOLOGIC_TIME')),
	               s:registerOutParameter($insertSpecimenStmt, 14, t:INTEGER()),
		       $insertSpecimenStmt)"/>
	    <xsl:with-param name="insertClinicalVariableStmt"
              xmlns:s="java:java.sql.CallableStatement"
              xmlns:t="java:java.sql.Types"
              xmlns:w="java:org.regenstrief.saxon.Workaround"
	      select="xwsf:progn(w:callableStatement_setLong($insertClinicalVariableStmt,1,rs:getLong($rset, 'INSTITUTION_ID')),
	               w:callableStatement_setLong($insertClinicalVariableStmt,2,rs:getLong($rset, 'PATIENT_ID')),
		       w:callableStatement_setTimestamp($insertClinicalVariableStmt,6,rs:getTimestamp($rset, 'PHYSIOLOGIC_TIME')),
		       s:registerOutParameter($insertClinicalVariableStmt,12,t:INTEGER()),
		       $insertClinicalVariableStmt)"/>
	   </xsl:apply-templates>
          </xsl:for-each>

	  <ex:catch exception="java.lang.Throwable">
            <xsl:call-template name="exd:dump">
	      <xsl:with-param name="name" select="exception"/>
	    </xsl:call-template>
	    <rollback
	        xmlns:c="java:java.sql.Connection"
                value="{c:rollback($connection)}"/>
	  </ex:catch>
      </ex:try>
    </saxon:while>
  </xsl:template>

<!-- MODE: upload -->

  <xsl:template mode="upload" match="/|@*|node()">
    <xsl:param name="orderId" select="/.."/>
    <xsl:param name="specimenId" select="/.."/>
    <xsl:param name="specimenSeq" select="/.."/>
    <xsl:param name="insertOrderStmt" select="/.."/>
    <xsl:param name="insertSpecimenStmt" select="/.."/>
    <xsl:param name="insertClinicalVariableStmt" select="/.."/>

    <xsl:apply-templates mode="upload" select="@*|node()">
      <xsl:with-param name="orderId" select="$orderId"/>
      <xsl:with-param name="specimenId" select="$specimenId"/>
      <xsl:with-param name="specimenSeq" select="$specimenSeq"/>
      <xsl:with-param name="insertOrderStmt" select="$insertOrderStmt"/>
      <xsl:with-param name="insertSpecimenStmt" select="$insertSpecimenStmt"/>
      <xsl:with-param name="insertClinicalVariableStmt" 
                      select="$insertClinicalVariableStmt"/>
    </xsl:apply-templates>
  </xsl:template>  

  <xsl:template mode="upload" match="spextract">
    <xsl:param name="insertOrderStmt" select="/.."/>
    <xsl:param name="insertSpecimenStmt" select="/.."/>
    <xsl:param name="insertClinicalVariableStmt" select="/.."/>
    <xsl:param name="specimenId" select="/.."/>

    <xsl:variable name="orderId"
        xmlns:s="java:java.sql.CallableStatement"
	select="xwsf:closure(
			xwsf:progn(s:execute($insertOrderStmt),
				   s:getLong($insertOrderStmt,8)))"/>

    <xsl:variable name="work">
      <xsl:apply-templates mode="upload" select="node()">
        <xsl:with-param name="orderId" select="$orderId"/>
        <xsl:with-param name="insertOrderStmt" select="$insertOrderStmt"/>
        <xsl:with-param name="insertSpecimenStmt" 
	                select="$insertSpecimenStmt"/>
        <xsl:with-param name="insertClinicalVariableStmt" 
                        select="$insertClinicalVariableStmt"/>
      </xsl:apply-templates>
    </xsl:variable>

    <spextract id="{@id}">
      <xsl:choose>
        <xsl:when test="$work/*">
	  <xsl:sequence select="$work"/>
          <updateSpextractWork orderId="{$orderId}"
            xmlns:s="java:java.sql.PreparedStatement"
            xmlns:cs="java:java.sql.CallableStatement"
	    value="{xwsf:progn(
	               s:setLong($updateSpextractWorkStmt,1,$orderId),
	    	       s:setLong($updateSpextractWorkStmt,2,@id),
		       s:execute($updateSpextractWorkStmt))}">
            <!-- this may fail if can't read in parameters from
                 callable statement, but then it may work -->
          </updateSpextractWork>
          <commit 
            xmlns:c="java:java.sql.Connection"
	    value="{c:commit($connection)}"/>	     
        </xsl:when>
        <xsl:otherwise>
          <rollback
	    xmlns:c="java:java.sql.Connection"
            value="{c:rollback($connection)}"/>
          <updateSpextractWork 
            xmlns:s="java:java.sql.PreparedStatement"
	    xmlns:t="java:java.sql.Types"
	    value="{xwsf:progn(
		       s:setNull($updateSpextractWorkStmt,1,t:INTEGER()),
		       s:setLong($updateSpextractWorkStmt,2,@id),
		       s:execute($updateSpextractWorkStmt))}"/>
          <commit 
            xmlns:c="java:java.sql.Connection"
	    value="{c:commit($connection)}"/>	     
        </xsl:otherwise>
      </xsl:choose>
    </spextract>
  </xsl:template>

  <xsl:template mode="upload" match="specimen">
    <xsl:param name="orderId" select="/.."/>
    <xsl:param name="insertOrderStmt" select="/.."/>
    <xsl:param name="insertSpecimenStmt" select="/.."/>
    <xsl:param name="insertClinicalVariableStmt" select="/.."/>

	<!--	 ps:println(sys:err(), tissue-type/@displayName),
			 ps:println(sys:err(), collection-method/@displayName),
			 ps:println(sys:err(), site-modifier/@displayName), -->
    <xsl:variable name="specimenId"
        xmlns:s="java:java.sql.CallableStatement"
	xmlns:t="java:java.sql.Types"
	xmlns:sys="java:java.lang.System"
	xmlns:ps="java:java.io.PrintStream"
        xmlns:w="java:org.regenstrief.saxon.Workaround"
        select="xwsf:closure(xwsf:progn(
		 w:callableStatement_setLong($insertSpecimenStmt,3,$orderId),
	         if(tissue-type/@code and tissue-type/@code ne '') 
                   then (w:callableStatement_setString($insertUMLSConceptStmt, 1, tissue-type/@code),
		         w:callableStatement_setString($insertUMLSConceptStmt, 2, tissue-type/@code),
		         w:callableStatement_setString($insertUMLSConceptStmt, 3, tissue-type/@displayName),
			 s:execute($insertUMLSConceptStmt),
		         w:callableStatement_setString($insertSpecimenStmt,5, tissue-type/@code),
		         w:callableStatement_setString($insertSpecimenStmt,6, tissue-type/@code))
                   else (s:setNull($insertSpecimenStmt,5,t:VARCHAR()),
		         s:setNull($insertSpecimenStmt,6,t:VARCHAR())),
		 if(tissue-type/text/text())
                   then w:callableStatement_setString($insertSpecimenStmt,7, tissue-type/text/text())
                   else s:setNull($insertSpecimenStmt,7,t:VARCHAR()),
		 if(collection-method/@code)
		   then (w:callableStatement_setString($insertUMLSConceptStmt, 1, collection-method/@code),
		         w:callableStatement_setString($insertUMLSConceptStmt, 2, collection-method/@code),
                         w:callableStatement_setString($insertUMLSConceptStmt, 3, collection-method/@displayName),
                         s:execute($insertUMLSConceptStmt),
                         w:callableStatement_setString($insertSpecimenStmt,8, collection-method/@code),
                         w:callableStatement_setString($insertSpecimenStmt,9, collection-method/@code))
                   else (s:setNull($insertSpecimenStmt,8,t:VARCHAR()),
			 s:setNull($insertSpecimenStmt,9,t:VARCHAR())),
		 if(collection-method/text/text())
                   then w:callableStatement_setString($insertSpecimenStmt,10, collection-method/text/text())
                   else s:setNull($insertSpecimenStmt,10,t:VARCHAR()),
                 if(site-modifier/@code)
		   then (w:callableStatement_setString($insertUMLSConceptStmt, 1, site-modifier/@code),
			 w:callableStatement_setString($insertUMLSConceptStmt, 2, site-modifier/@code),
                         w:callableStatement_setString($insertUMLSConceptStmt, 3, site-modifier/@displayName),
                         s:execute($insertUMLSConceptStmt),
                         w:callableStatement_setString($insertSpecimenStmt,11, site-modifier/@code),
                         w:callableStatement_setString($insertSpecimenStmt,12, site-modifier/@code))
		   else (s:setNull($insertSpecimenStmt,11,t:VARCHAR()),
			 s:setNull($insertSpecimenStmt,12,t:VARCHAR())),
		 if(site-modifier/text/text())
		   then w:callableStatement_setString($insertSpecimenStmt,13, site-modifier/text/text())
                   else s:setNull($insertSpecimenStmt,13,t:VARCHAR()),
		 s:execute($insertSpecimenStmt),
		 s:getLong($insertSpecimenStmt,14)))"/>

    <xsl:variable name="work">
      <xsl:apply-templates mode="upload" select="node()">
        <xsl:with-param name="orderId" select="$orderId"/>
        <xsl:with-param name="specimenId" select="$specimenId"/>
        <xsl:with-param name="specimenSeq" select="position()"/>
        <xsl:with-param name="insertOrderStmt" select="$insertOrderStmt"/>
        <xsl:with-param name="insertSpecimenStmt" 
	  select="$insertSpecimenStmt"/>
        <xsl:with-param name="insertClinicalVariableStmt" 
                      select="$insertClinicalVariableStmt"/>
      </xsl:apply-templates>
    </xsl:variable>

    <xsl:if test="$work/*">
      <insertSpecimen id="{$specimenId}">
        <xsl:sequence select="$work"/>
      </insertSpecimen>
    </xsl:if>
  </xsl:template>

  <xsl:template mode="upload"
      match="observation[ancestor::specimen
                         and code/@displayName='Tissue-DX'
                         and value/@code
	   	         and not(negationInd/@value='true')
			 and not(uncertaintyInd/@value='true')]">
    <xsl:param name="orderId" select="/.."/>
    <xsl:param name="specimenId" select="/.."/>
    <xsl:param name="specimenSeq" select="/.."/>
    <xsl:param name="insertOrderStmt" select="/.."/>
    <xsl:param name="insertSpecimenStmt" select="/.."/>
    <xsl:param name="insertClinicalVariableStmt" select="/.."/>
    <xsl:variable name="clinicalVariableId"
        xmlns:s="java:java.sql.CallableStatement"
	xmlns:sys="java:java.lang.System"
	xmlns:ps="java:java.io.PrintStream"
        xmlns:w="java:org.regenstrief.saxon.Workaround"
	select="xwsf:progn(w:callableStatement_setLong($insertClinicalVariableStmt,3,$orderId),
	         w:callableStatement_setLong($insertClinicalVariableStmt,4,$orderId),
	         w:callableStatement_setLong($insertClinicalVariableStmt,5,$specimenId),
                 w:callableStatement_setString($insertUMLSConceptStmt, 1, value/@code),
                 w:callableStatement_setString($insertUMLSConceptStmt, 2, value/@code),
                 w:callableStatement_setString($insertUMLSConceptStmt, 3, value/@displayName),
                 s:execute($insertUMLSConceptStmt),
                 w:callableStatement_setString($insertClinicalVariableStmt,7,value/@code),
	         w:callableStatement_setString($insertClinicalVariableStmt,8,value/@displayName),
		 w:callableStatement_setString($insertClinicalVariableStmt,9,text/text()),
		 w:callableStatement_setLong($insertClinicalVariableStmt,10,position()),
		 w:callableStatement_setString($insertClinicalVariableStmt,11,concat(format-number($specimenSeq,'000'),'.',format-number(position(),'000'))),
		 s:execute($insertClinicalVariableStmt),
		 s:getLong($insertClinicalVariableStmt,12))"/>
    <insertClinicalVariable id="{$clinicalVariableId}"
       orderId="{$orderId}" 
       specimenId="{$specimenId}"
       sequenceNumber="{position()}"/>
  </xsl:template>

<!-- MODE: makeWhereClause -->

<xsl:template mode="makeWhereClause" match="report/@id">
  <xsl:text> AND MULTIMEDIA_CONTENT_ID >= </xsl:text>
  <xsl:value-of select="."/>
</xsl:template>

<xsl:template mode="makeWhereClause" match="report/@code">
  <xsl:text> AND ( SERVICE_SYS_ID=1 AND SERVICE_CODE IN (''</xsl:text>
  <xsl:for-each select="tokenize(., '\s+')">
     <xsl:text>, '</xsl:text>
     <xsl:value-of select="."/>
     <xsl:text>'</xsl:text>
  </xsl:for-each>
  <xsl:text>) )</xsl:text>
</xsl:template>

<xsl:template mode="makeWhereClause" match="report/@patientId">
  <xsl:text> AND PATIENT_ID = </xsl:text>
  <xsl:value-of select="."/>
</xsl:template>

<xsl:template mode="makeWhereClause" match="report/@orderId">
  <xsl:text> AND MEDICAL_ORDER_ID = </xsl:text>
  <xsl:value-of select="."/>
</xsl:template>

<xsl:template mode="makeWhereClause" match="report/@institutionId">
  <xsl:text> AND INSTITUTION_ID = </xsl:text>
  <xsl:value-of select="."/>
</xsl:template>

<xsl:template mode="makeWhereClause" match="/|@*|node()">
  <xsl:apply-templates mode="makeWhereClause" select="@*|node()"/>
</xsl:template>


<!-- MODE: text -->

<xsl:template mode="text" match="/|@*|node()">
  <xsl:copy>
    <xsl:apply-templates mode="text" select="@*|node()"/>
  </xsl:copy>
</xsl:template>

<xsl:template mode="text" match="text_report">
  <text>
    <xsl:apply-templates mode="text" select="@*|node()"/>    
  </text>
</xsl:template>

<xsl:template mode="text" match="text">
  <p>
    <xsl:apply-templates mode="text" select="@*|node()"/>    
  </p>
</xsl:template>

</xsl:transform>