/**
 * MySQL 兼容函数
 */
-- STRCMP
drop function if exists STRCMP(lvarchar,lvarchar);
create dba function STRCMP(str1 lvarchar, str2 lvarchar)
returns int
with (not variant)
  if str1 = str2 then
    return 0;
  elif str1 > str2 then
    return 1;
  else 
    return -1;
  end if;
end function;

-- CURDATE()/CURRENT_DATE()返回当前日期
drop function if exists curdate();
create dba function CURDATE()
returns varchar(10)
with (not variant)
  return gbase_to_char(today,'%Y-%m-%d');
end function;

drop function if exists CURRENT_DATE();
create dba function CURRENT_DATE()
returns char(10)
with (not variant)
  return gbase_to_char(today,'%Y-%m-%d');
end function;

-- CURRENT_TIME()/CURTIME()返回当前时间
drop function if exists CURTIME();
create dba function CURTIME()
returns char(8)
with (not variant)
  return gbase_to_char(current year to second, '%H:%M:%S');
end function;

drop function if exists CURRENT_TIME();
create dba function CURRENT_TIME()
returns char(8)
with (not variant)
  return gbase_to_char(current year to second, '%H:%M:%S');
end function;

-- CURRENT_TIMESTAMP()返回当前日期和时间
drop function if exists CURRENT_TIMESTAMP();
create dba function CURRENT_TIMESTAMP()
returns char(19)
with (not variant)
  return gbase_to_char(current year to second, '%Y-%m-%d %H:%M:%S');
end function;

-- ADDTIME(t,n)时间 t 加上 n 秒的时间
drop function if exists ADDTIME(datetime year to second, int);
create dba function ADDTIME(t datetime year to second, n int)
returns datetime year to second
with (not variant)
  return t + n units second;
end function;

-- DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
drop function if exists DATEDIFF(datetime year to second, datetime year to second);
create dba function DATEDIFF(d1 datetime year to second, d2 datetime year to second)
returns int
with (not variant)
  return substr(d1 - d2,1,10);
end function;

-- DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday
-- WEEKDAY
drop function if exists DAYNAME(datetime year to second, varchar);
create dba function DAYNAME(d1 datetime year to second, lc varchar(10) default 'EN')
returns varchar(20)
with (not variant)
  define tmpstr_en varchar(20);
  define tmpstr_cn varchar(20);
  case weekday(d1)
    when 0 then
	  let tmpstr_en = 'Sunday';
	  let tmpstr_cn = '星期天';
	when 1 then
	  let tmpstr_en = 'Monday';
	  let tmpstr_cn = '星期一';
	when 2 then
	  let tmpstr_en = 'Tuesday';
	  let tmpstr_cn = '星期二';
	when 3 then
	  let tmpstr_en = 'Wednesday';
	  let tmpstr_cn = '星期三';
	when 4 then
	  let tmpstr_en = 'Thursday';
	  let tmpstr_cn = '星期四';
	when 5 then
	  let tmpstr_en = 'Friday';
	  let tmpstr_cn = '星期五';
	else
	  let tmpstr_en = 'Saturday';
	  let tmpstr_cn = '星期六';
  end case;
  if lower(lc) = 'cn' or lc = '中文' then
    return tmpstr_cn;
  else
    return tmpstr_en;
  end if;
end function;

-- DAYOFMONTH(d)计算日期 d 是本月的第几天
drop function if exists DAYOFMONTH(datetime year to second);
create dba function DAYOFMONTH(d1 datetime year to second)
returns int
with (not variant)
  return day(d1);
end function;

-- DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推
drop function if exists DAYOFWEEK(datetime year to second);
create dba function DAYOFWEEK(d1 datetime year to second)
returns int
with (not variant)
  return weekday(d1) + 1;
end function;

-- DAYOFYEAR(d)计算日期 d 是本年的第几天
drop function if exists DAYOFYEAR(datetime year to second);
create dba function DAYOFYEAR(d1 datetime year to second)
returns int
with (not variant)
  return trunc(1 + (d1 - TRUNC(d1, 'YEAR')));
end function;

-- WEEKOFMONTH(d) 计算当月第几周
drop function if exists weekofmonth(datetime year to second);
-- day of month, first week is 01~07
create dba function weekofmonth(d1 datetime year to second) 
returns int 
with (not variant)
  return trunc(1 + (d1 - TRUNC(d1, 'MONTH')) / 7);
end function;

-- WEEKOFYEAR(d) 计算当年第几周
drop function if exists weekofyear(datetime year to second);
-- day of year, first week is 01-01 ~ 01-07
create dba function weekofyear(d1 datetime year to second) 
returns int 
with (not variant)
  return trunc(1 + (d1 - TRUNC(d1, 'YEAR')) / 7);
end function;


-- TIMESTAMPDIFF(unit, d1, d2)
drop function if exists TIMESTAMPDIFF(varchar, datetime year to second, datetime year to second, boolean);
-- SECOND,MINUTE,HOUR,DAY；d2>d1为正数
create dba function TIMESTAMPDIFF(unit varchar(10), d1 datetime year to second, d2 datetime year to second, isabs boolean default 'f')
returns int8 
with (not variant)
  define tmp_num int8;
  define tmp_str varchar(20);
  define tmp_flag char(1);
  define tmp_y int;
  define tmp_m int;
  define tmp_d int;
  
  let tmp_str = d2 - d1;
  let tmp_num = substr(tmp_str,1,9)::int8; 
  let tmp_flag = 'f';
  if tmp_num < 0 then 
	let tmp_flag = 't';
  end if;
  case lower(unit)	  
    when 'day' then
	  let tmp_num = tmp_num;
	when 'hour' then
	  let tmp_num = abs(tmp_num) * 24 + substr(tmp_str,11,2)::int8;
	when 'minute' then 
	  let tmp_num = (abs(tmp_num) * 24 + substr(tmp_str,11,2)::int8) * 60 + substr(tmp_str,14,2);
	when 'second' then 
	  let tmp_num = ((abs(tmp_num) * 24 + substr(tmp_str,11,2)::int8) * 60 + substr(tmp_str,14,2)) * 60 + substr(tmp_str,17,2);
	else
	  let tmp_num = 0;
  end case;
 
  if tmp_flag = 't' and isabs = 'f' then
    return '-' || tmp_num;
  else
    return tmp_num;
  end if;
end function;





