/*******************************************************************************
 * FreeAdhocUDF - copyright (c) 2004 - 2009 adhoc dataservice GmbH
 * Christoph Theuring ct / Peter Mandrella pm / Georg Horn gh
 * <http://freeadhocudf.org> eMail <help@freeadhocudf.org>
 * (bug-)reports only in forum http://freeadhocudf.org/portal 
 * or eMail to help@freeadhocudf.org
 *   
 * this file contains declarations of all new functions from FreeAdhocUDF
 * from version adhoc20090128
 * use from FireBird 2.0 - possibility to return NULL
 *  
 * if you use this declarations in a non UTF-8 or UNICODE_FSS database 
 *		the max lenght of CSTRING is 32766 (1 Byte per character)
 * if you use this declarations in a InterBase UNICODE_FSS database 
 *		the max lenght of CSTRING is 10922 (3 Byts per character)
 * if you use this declarations in a InterBase or FireBird UTF-8 database 
 *		the max lenght of CSTRING is 8191 (max 4 Byts per character)
 * All declarations in this file are set to CSTRING(8191)
 * if you want to use one UDF more times in one piped string or one SQL, 
 * the sum of CSTRING of the used UDF doesn't grow over the max of your database
 *
 * Functions in group and alphabetical order
 *
 * This file is part of FreeAdhocUDF.
 * FreeAdhocUDF is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * FreeAdhocUDF 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 Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public License
 * along with FreeAdhocUDF.  If not, see <http://www.gnu.org/licenses/>. 
*******************************************************************************/

SET SQL DIALECT 3;

/******************************************************************************/
/****                User Defined Functions - checksum functions           ****/
/****            use from FireBird 2.0 - possibility to return NULL        ****/
/******************************************************************************/

DECLARE EXTERNAL FUNCTION F_CROSSSUM
    INTEGER NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'crosssum' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_CROSSDIFF
    INTEGER NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'crossdiff' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_WEIGHTCROSSSUM
    INTEGER NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'weightcrosssum' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_ITERATECROSSSUM
    INTEGER NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'iteratecrosssum' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_GTIN8CHECK
    CSTRING(255) NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'gtin8check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_UPC12CHECK
    CSTRING(255) NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'upc12check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_GTIN13CHECK
    CSTRING(255) NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'gtin13check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_GTIN14CHECK
    CSTRING(255) NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'gtin14check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_NVE18CHECK
    CSTRING(255) NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'nve18check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_GTIN8
    CSTRING(255) NULL
    RETURNS CSTRING(8) FREE_IT
ENTRY_POINT 'gtin8' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_UPC12
    CSTRING(255) NULL
    RETURNS CSTRING(12) FREE_IT
ENTRY_POINT 'upc12' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_GTIN13
    CSTRING(255) NULL
    RETURNS CSTRING(13) FREE_IT
ENTRY_POINT 'gtin13' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_GTIN14
    CSTRING(255) NULL
    RETURNS CSTRING(14) FREE_IT
ENTRY_POINT 'gtin14' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_NVE18
    CSTRING(255) NULL
    RETURNS CSTRING(18) FREE_IT
ENTRY_POINT 'nve18' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_ISBN10CHECK
    CSTRING(255) NULL
RETURNS INTEGER FREE_IT
ENTRY_POINT 'isbn10check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_ISBN10
    CSTRING(255) NULL
    RETURNS CSTRING(13) FREE_IT
ENTRY_POINT 'isbn10' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_ISBN13CHECK
    CSTRING(255) NULL
RETURNS INTEGER FREE_IT
ENTRY_POINT 'isbn13check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_ISBN13
    CSTRING(255) NULL
    RETURNS CSTRING(17) FREE_IT
ENTRY_POINT 'isbn13' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_ISSN8CHECK
    CSTRING(255) NULL
RETURNS INTEGER FREE_IT
ENTRY_POINT 'issn8check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_ISSN8
    CSTRING(255) NULL
    RETURNS CSTRING(9) FREE_IT
ENTRY_POINT 'issn8' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_UPSTNCHECK
    CSTRING(255) NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'upstncheck' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_UPSTN
    CSTRING(255) NULL
    RETURNS CSTRING(21) FREE_IT
ENTRY_POINT 'upstn' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_2OF5CHECK
    CSTRING(255) NULL
    RETURNS INTEGER FREE_IT
ENTRY_POINT 'twooffivecheck' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_2OF5
    CSTRING(255) NULL
    RETURNS CSTRING(255) FREE_IT
ENTRY_POINT 'twooffive' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_CODE39CHECK
    CSTRING(255) NULL
    RETURNS CSTRING(255) FREE_IT
    ENTRY_POINT 'code39check' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_CODE39
    CSTRING(255) NULL
    RETURNS CSTRING(255) FREE_IT
    ENTRY_POINT 'code39' MODULE_NAME 'FreeAdhocUDF';

/******************************************************************************/
/****              User Defined Functions - converting functions           ****/
/******************************************************************************/

DECLARE EXTERNAL FUNCTION F_C_OBU2G
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'obu2g' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_G2OBU
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'g2obu' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_DRA2G
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'dra2g' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_G2DRA
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'g2dra' MODULE_NAME 'FreeAdhocUDF';    

DECLARE EXTERNAL FUNCTION F_C_GBBU2L
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'gbbu2l' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_L2GBBU
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'l2gbbu' MODULE_NAME 'FreeAdhocUDF';    

DECLARE EXTERNAL FUNCTION F_C_USBU2L
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'usbu2l' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_L2USBU
    DOUBLE PRECISION NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'l2usbu' MODULE_NAME 'FreeAdhocUDF';    

DECLARE EXTERNAL FUNCTION F_C_TIME2DECTIME
    TIMESTAMP NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'time2dectime' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_DECTIME2TIME
    DOUBLE PRECISION NULL
    RETURNS TIME FREE_IT
    ENTRY_POINT 'dectime2time' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_TIME2INDTIME
    TIMESTAMP NULL
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'time2indtime' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_C_INDTIME2TIME
    DOUBLE PRECISION NULL
    RETURNS TIME FREE_IT
    ENTRY_POINT 'indtime2time' MODULE_NAME 'FreeAdhocUDF';

/******************************************************************************/
/****                 User Defined Functions: Date and Time                ****/
/****            use from FireBird 2.0 - possibility to return NULL        ****/
/******************************************************************************/

DECLARE EXTERNAL FUNCTION F_INDMINUTESBETWEEN
    TIMESTAMP NULL,
    TIMESTAMP NULL
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'indminutesbetween' MODULE_NAME 'FreeAdhocUDF';

/* for FireBird */
DECLARE EXTERNAL FUNCTION F_INDMINUTESBETWEENTHR
    TIMESTAMP NULL,
    TIMESTAMP NULL,
    TIME null,
    INTEGER NULL,
    TIME null,
    INTEGER NULL
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'indminutesbetweenthr' MODULE_NAME 'FreeAdhocUDF';

/* for InterBase 
DECLARE EXTERNAL FUNCTION F_INDMINUTESBETWEENTHR
    TIMESTAMP,
    TIMESTAMP,
    TIMESTAMP,
    INTEGER,
    TIMESTAMP,
    INTEGER
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'indminutesbetweenthrib' MODULE_NAME 'FreeAdhocUDF'
*/
DECLARE EXTERNAL FUNCTION F_FIRSTDAYWEEK
   TIMESTAMP NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'firstdayweek' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_LASTDAYWEEK
   TIMESTAMP NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'lastdayweek' MODULE_NAME 'FreeAdhocUDF';
       
DECLARE EXTERNAL FUNCTION F_FIRSTDAYWEEKISO
   TIMESTAMP NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'firstdayweekiso' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_LASTDAYWEEKISO
   TIMESTAMP NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'lastdayweekiso' MODULE_NAME 'FreeAdhocUDF';
       
DECLARE EXTERNAL FUNCTION F_FIRSTDAYQUARTER
   TIMESTAMP NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'firstdayquarter' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_LASTDAYQUARTER
   TIMESTAMP NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'lastdayquarter' MODULE_NAME 'FreeAdhocUDF';
         
DECLARE EXTERNAL FUNCTION F_FIRSTDAYKW
   INTEGER NULL,
   INTEGER NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'firstdaykw' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_LASTDAYKW
   INTEGER NULL,
   INTEGER NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'lastdaykw' MODULE_NAME 'FreeAdhocUDF';
      
DECLARE EXTERNAL FUNCTION F_FIRSTDAYWOY
   INTEGER NULL,
   INTEGER NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'firstdaywoy' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_LASTDAYWOY
   INTEGER NULL,
   INTEGER NULL
   RETURNS TIMESTAMP FREE_IT
   ENTRY_POINT 'lastdaywoy' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_AGEINWORKINGDAYS
    TIMESTAMP NULL,
    TIMESTAMP NULL,
    INTEGER NULL
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'ageinworkingdays' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_AGEINWORKINGDAYSTHRESHOLD
    TIMESTAMP NULL,
    TIMESTAMP NULL,
    INTEGER NULL,
    INTEGER NULL,
    INTEGER NULL,
    INTEGER NULL,
    INTEGER NULL
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'ageinworkingdaysthreshold' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_WORKINGDAYSBETWEEN
    TIMESTAMP NULL,
    TIMESTAMP NULL,
    INTEGER NULL
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'workingdaysbetween' MODULE_NAME 'FreeAdhocUDF';

/******************************************************************************/
/****                   User Defined Functions - other functions           ****/
/****            use from FireBird 2.0 - possibility to return NULL        ****/
/******************************************************************************/

DECLARE EXTERNAL FUNCTION F_SNVL
    CSTRING(8191) NULL, 
    CSTRING(8191) NULL   
    RETURNS CSTRING(8191) FREE_IT
    ENTRY_POINT 'snvl' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_SNVL2
    CSTRING(8191) NULL, 
    CSTRING(8191) NULL, 
    CSTRING(8191) NULL   
    RETURNS CSTRING(8191) FREE_IT
    ENTRY_POINT 'snvl2' MODULE_NAME 'FreeAdhocUDF';
    
DECLARE EXTERNAL FUNCTION F_INVL
    INTEGER NULL, 
    INTEGER NULL   
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'invl' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_INVL2
    INTEGER NULL, 
    INTEGER NULL, 
    INTEGER NULL   
    RETURNS INTEGER FREE_IT
    ENTRY_POINT 'invl2' MODULE_NAME 'FreeAdhocUDF';    

DECLARE EXTERNAL FUNCTION F_DNVL
    DOUBLE PRECISION NULL, 
    DOUBLE PRECISION NULL   
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'dnvl' MODULE_NAME 'FreeAdhocUDF';

DECLARE EXTERNAL FUNCTION F_DNVL2
    DOUBLE PRECISION NULL, 
    DOUBLE PRECISION NULL, 
    DOUBLE PRECISION NULL   
    RETURNS DOUBLE PRECISION FREE_IT
    ENTRY_POINT 'dnvl2' MODULE_NAME 'FreeAdhocUDF'; 

/* adhoc 2009-01-28 */
