dnl Copyright (C) 2013 The Meme Factory, Inc.  http://www.meme.com/
dnl
dnl    This file is part of Gombe-MI.
dnl
dnl    Gombe-MI is free software; you can redistribute it and/or modify
dnl    it under the terms of the GNU General Public License as published by
dnl    the Free Software Foundation; either version 3 of the License, or
dnl    (at your option) any later version.
dnl
dnl    This program is distributed in the hope that it will be useful,
dnl    but WITHOUT ANY WARRANTY; without even the implied warranty of
dnl    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
dnl    GNU General Public License for more details.
dnl
dnl    You should have received a copy of the GNU General Public License
dnl    along with Gombe-MI.  If not, see <http://www.gnu.org/licenses/>.
dnl
dnl Function to convert an animid in the access db.
dnl
dnl Karl O. Pinc <kop@meme.com>

dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`types.m4')
include(`macros.m4')dnl

dnl Reset our search path so we pick up the conversion schema.
define(`gmi_function_search_path', `prepared_data, gombemi, pg_temp')

CREATE OR REPLACE FUNCTION prepared_data.convert_animid(this_animid VARCHAR(gmi_animid_len)
                                        , this_date DATE)
  RETURNS VARCHAR(gmi_animid_len)
  LANGUAGE plpgsql
  IMMUTABLE
  SECURITY DEFINER
  gmi_function_set_search_path
  AS $$
  -- Convert a mis-identified animid.
  --
  -- GPL_notice(`  --', `2013', `The Meme Factory, Inc.  http://www.meme.com/')
  --
  -- Syntax: convert_animid(this_animid, this_date);
  --
  -- Arguments:
  --  this_animid  The animid to convert
  --  this_date    Date of the data in question.
  --
  -- Returns:
  --   The right animid.
  --
  -- Remarks:
  --   One-off program.  Once it works we won't run it again.

  DECLARE
  BEGIN
    CASE this_animid
      WHEN 'CN' THEN
        IF this_date >= '2001-12-02' AND this_date <= '2004-12-31' THEN
          RETURN 'BAH';
        END IF;
      WHEN 'CF' THEN
        IF this_date >= '1995-03-02' THEN
          IF this_date <= '2001-12-01' THEN
            RETURN 'UNK';
          ELSIF this_date <= '2004-12-31' THEN
            RETURN 'BAH';
          END IF;
        END IF;
      WHEN 'TT' THEN
        IF this_date >= '1998-02-06' AND this_date <= '2008-03-02' THEN
          RETURN 'TTA';
        END IF;
      WHEN 'WD' THEN
        IF this_date >= '2001-01-01' AND this_date <= '2013-07-02' THEN
          RETURN 'SIF';
        END IF;
      ELSE
        NULL;
    END CASE;

    RETURN this_animid;

  END;
$$;


grant_func_priv(`prepared_data.convert_animid(VARCHAR(gmi_animid_len), DATE)')