Adds a determination to every occurrence of taxa found in a specified CSV. The taxa must be defined in a column called 'Taxon_Name'.

TaxonDeterminationUpdate.xml

<?xml version="1.0" ?>
<!--   
     XML Batch Update by Mike Weideli of Littlefield Consultancy
     http://www.lfield.co.uk
    January 2010
    
    Modifications by Charles Roper of Sussex Biodiversity Record Centre
    http://sxbrc.org.uk
    January 2010 
    
    Note:   The specified CSV is loaded into temporary table #CSV1. The CSV
    should contain one column - Taxon_name - that is populated with the names of
    the species for which we are to add new determinations to. -->

        
<batchupdate 
  menupath    = "Sussex\Determinations"
  title       = "Add Determinations to Taxa from CSV" 
  description = "Adds a determination to every occurrence of taxa found in a specified CSV. The taxa must be defined in a column called 'Taxon_Name'.">

<CSVTables>
  <CSVTable name="#CSV1">
  <CSVFile description="Validation file" />
  <CSVColumn name="Taxon_Name" datatype="varchar" size="100" />
  </CSVTable>
</CSVTables>
 
<SQL>

<Where keytype="Default">

DECLARE
@occurrence_key       char(16)
,@new_determination   varchar(100)
,@comment             varchar(100)
,@tdet_key            char(16)
,@Entry_date          DateTime
,@Entered_by          char(16)
,@Vague_Date_Start    int
,@Vague_Date_End      int
,@Vague_Date_Type     varchar(2)
,@Preferred           bit
,@Determiner          char(16)
,@Detdate             char(10)
,@Det_type_key        char(16)
,@Det_role_key        char(16)
,@Custodian           char(8)
,@TLIK                char(16)

SET @Preferred  = 1

-- Get the entry date for the new determination
SET @Entry_date = GETDATE()

-- Get the custodian from the current SiteID
SET @Custodian = (
  SELECT data FROM setting WHERE Name = 'SiteID'
)

-- Get the NAME_KEY of the currently logged-in user to use as the ENTERED_BY
-- value for the new determination.
SET @Entered_by = (
  SELECT name_key FROM name WHERE
    <Condition field    = "Name_key"
               type     = "CurrentUserID"
               name     = "Current User ID"
               operator = "equal" /> 
)

-- Get the NAME_KEY of the new determiner.
SET @Determiner = ( 
  SELECT name_key FROM name WHERE 
    <Condition field      = "Name_key"
               operator   = "equal"
               type       = "Individual" 
               entrycount = "1"  
               name       = "Determiner" /> 
)

-- Specify the determiner role key, based on an option list.
SET @Det_role_key = (
  SELECT determiner_role_key FROM determiner_role WHERE 
    <Condition field    = "determiner_role.determiner_role_key"
               operator = "equal"
               type     = "OptionSet"  
               name     = "Determiner role" >
      <Option name="County recorder" value="'NBNSYS0000000004'" />
      <Option name="Specialist"      value="'NBNSYS0000000001'" />
    </Condition>
)

-- Specify the determination type. Must be typed manually for now, based on a
-- value found in the DETERMINATION_TYPE.SHORT_NAME column
SET @Det_type_key = (
  SELECT determination_type_key FROM determination_type WHERE
    <Condition field    = "SHORT_NAME"
               operator = "equal" 
               type     = "Text" 
               name     = "Determination type" />
)

-- Specify the new determination date
SET @detdate <Condition field    = ""
                        operator = "equal"
                        type     = "Text"
                        name     = "DeterminationDate (dd/mm/yyyy)" />

-- Convert the new determination date to vague date format                          
SET @vague_date_type = 'D'
SET @vague_date_start = dbo.LCToRataDie(@DetDate)
SET @vague_date_end   = dbo.LCToRataDie(@DetDate)

-- Specify the determination comment
SET @comment <Condition field    = ""
                        operator = "equal"
                        type     = "Text"
                        name     = "Comment" />
                        
-- Declare a cursor that contains all TAXON_OCCURRENCE_KEYS of occurrences for
-- species found in the specified CSV.
DECLARE CSV1_Cursor CURSOR FOR (
  SELECT 
    tocc.taxon_occurrence_key
  FROM
    taxon_occurrence tocc
  INNER JOIN 
    taxon_determination tdet ON
      tocc.taxon_occurrence_key = tdet.taxon_occurrence_key AND
      tdet.Preferred = 1
  INNER JOIN 
    Index_taxon_name itn ON
      itn.taxon_list_item_key = tdet.taxon_List_item_key
  INNER JOIN
    Index_Taxon_Name as itn1 ON
      itn1.taxon_list_item_key = itn.recommended_taxon_list_item_key
  INNER JOIN #CSV1 ON
    -- Join the list of names from the temp #CSV1 table to the recommended
    -- taxon name.
    #CSV1.taxon_name = itn1.preferred_name 
  WHERE
    -- Exclude 'Invalid' determinations; i.e., if a record has been invalidated
    -- do not add a new determination.
    tdet.determination_type_key &#60;&#62; 'NBNSYS0000000001'
  AND
    -- Exclude records already determined by this determiner. Rationale: if this
    -- determiner has already looked at a record (i.e., he's already made a
    -- determination) then we don't want to override that existing determination
    -- with a new, possibly incorrect, one.
    tdet.determiner &#60;&#62; @Determiner
)
FOR READ ONLY

/*
  Loop through the CSV data and do the following on each iteration:
    1. Generate key for a new taxon_determination record
    2. Get the TAXON_LIST_ITEM_KEY for the current occurrence
    3. Update the existing determination to make it non-preferred
    4. Insert the new determination
    5. Update the VERIFIED flag in TAXON_OCCURRENCE to match the new det.
*/
OPEN CSV1_Cursor
FETCH NEXT FROM CSV1_Cursor
INTO @occurrence_key

  WHILE @@FETCH_STATUS = 0
  BEGIN
    
    -- Generate next key for the taxon_determination table
    EXECUTE spNextKey 'Taxon_Determination', @tdet_key OUTPUT 
  
    -- Get the taxon_list_item_key of the @occurrence (taxon_occurrence_key) 
    -- from the preferred taxon_determination the current cursor position.
    SET @TLIK = (
      SELECT
        Taxon_List_Item_Key 
      FROM
        Taxon_determination TD 
      WHERE
        taxon_occurrence_key = @occurrence_key
      AND
        TD.preferred = 1
    )
    
    -- Update the existing determination to make it non-preferred.
    UPDATE
      Taxon_determination
    SET
      preferred = 0
    WHERE
      taxon_occurrence_key = @occurrence_key
    
    -- Insert the new determination into the taxon_determination table
    INSERT INTO
      taxon_determination
        values (
          @tdet_key
          ,@TLIK
          ,@occurrence_key
          ,@vague_date_start
          ,@vague_date_end
          ,@vague_date_type
          ,@comment
          ,@Preferred
          ,@Determiner
          ,@Det_type_key
          ,@Det_role_key
          ,@Entered_by
          ,@Entry_date
          ,null
          ,null
          ,null
          ,@Custodian
        )
    
    -- Update the taxon_occurrence verified flag to match that of the new 
    -- preferred determination
    UPDATE
      taxon_occurrence
    SET
      verified = dt.verified 
    FROM
      taxon_determination txd
    INNER JOIN
      taxon_occurrence txo ON
        txd.taxon_occurrence_key = txo.taxon_occurrence_key AND
        txd.preferred = 1
    INNER JOIN
      determination_type dt ON
        dt.determination_type_key = txd.determination_type_key 
    WHERE
      txo.taxon_occurrence_key = @occurrence_key
  
    FETCH NEXT FROM CSV1_Cursor
      INTO @occurrence_key
  
  END

  CLOSE      CSV1_Cursor
  DEALLOCATE CSV1_Cursor

</Where>
</SQL>
</batchupdate>