--
-- The Postgres specific hacks for emulating oracle date/char conversion 
-- functions.
--
-- Uses/requires pltcl. configure postgres using --with-tcl and copy pltcl.so 
-- to /usr/local/pgsql/lib.

create function pltcl_call_handler() returns opaque
	as '/usr/local/pgsql/lib/pltcl.so'
	language 'C';

create trusted procedural language 'pltcl'
	handler pltcl_call_handler
	lancompiler 'PL/Tcl';

create function trunc(datetime,varchar) returns datetime as '
declare
        date_in         alias for $1;
        part            alias for $2;
begin
        if lower(part) = ''yyyy'' then
           return date_trunc(''year'',date_in);
        end if;
        return date_trunc(part,date_in);
end;
' language 'plpgsql';

create function trunc(timespan) returns varchar as '
declare
	inspan          alias for $1;
begin
        return date_part(''day'',inspan);
end;
' language 'plpgsql';

create function last_day(datetime) returns datetime as '
begin
       return date_trunc(''month'',(date_trunc(''month'',$1) + 31)::datetime) - 1;
end;
' language 'plpgsql';

create function calc_days(varchar) returns integer as '
       set span $1

       if { ![regexp {([0\-9]+) [0\-9][0\-9]:[0\-9][0\-9]:[0\-9][0\-9]} $span {} days] } {
          return 0
       }
       
       return $days
' language 'pltcl';


create function round(timespan) returns integer as '
declare
        delta   alias for $1;
        invar   varchar;
begin
        invar := delta;
        return calc_days(invar);
end;
' language 'plpgsql';

create function calc_months(varchar) returns integer as '

       set timespan $1

       if { ![regexp {([0\-9]+) years?} $timespan {} yr] } {
          set yr 0
       }

       if { ![regexp {([0\-9]+) mons?}  $timespan {} mn] } { 
          set mn 0
       }
       
       return [expr ($yr * 12) + $mn]

' language 'pltcl';


create function months_between(datetime, datetime) returns integer as '
declare
        date1   alias for $1;
        date2   alias for $2;
        delta   varchar;
begin
        delta := age(date1,date2);
        return calc_months(delta);
end;
' language 'plpgsql';


create function sign(timespan) returns integer as '

       if [regexp {ago} $1 {}] {
          return -1
       } else {
          return 1
       }

' language 'pltcl';

create function day_of_the_year(datetime) returns varchar as '
declare
        the_date        alias for $1;
        new_date        datetime;
        delta           varchar(80);
        pos_blank       integer;
begin
        if date_trunc(''day'',the_date) = date_trunc(''year'',the_date) then
           return ''1'';
        else
           new_date  := date_trunc(''day'',the_date + 1);
           delta     := new_date \- date_trunc(''year'',the_date);
           pos_blank := position('' '' in delta);
           return substring(delta from 0 for pos_blank);
        end if;
end;
' language 'plpgsql';

-- I think the julian date starts in 4713BC, but this 
-- date gives the correct answer.  I verified it by using two seperate
-- julian date calculators that I found by searching on the net.
create function julian_zero_date() returns datetime as '
begin
	return ''4714-11-25 BC''::datetime;
end;
' language 'plpgsql';

create function julian_date(datetime) returns varchar as '
declare
       indate    alias for $1;
begin
        return trunc(indate - julian_zero_date());
end;
' language 'plpgsql';


-- drop function to_char_work(datetime,varchar,varchar,varchar,varchar);
create function to_char_work(datetime,varchar,varchar,varchar,varchar) 
returns varchar as '

       set indate       $1
       set pattern      [string tolower $2]
       set day_of_year  $3
       set julian_date  $4
       set day_of_week  $5
       set month        ""
       set day          ""

       # pull apart the date/time string
       regexp {^([0\-9][0\-9]([0\-9][0\-9]))\-([0\-9][0\-9])\-([0\-9][0\-9]) ([0\-9][0\-9]):([0\-9][0\-9]):([0\-9][0\-9])} $indate full_date year part_year month day hour minute second
       
       # calculate the quarter
       switch \-regexp $month {
         "0[1-3]" { set qtr 1 }
         "0[4-6]" { set qtr 2 }
         "0[7-9]" { set qtr 3 }
         "1[0-2]" { set qtr 4 }
         default  { set qtr 0 }
       }
       
       switch $month {
         "01"    { set p_month January }
         "02"    { set p_month February }
         "03"    { set p_month March }
         "04"    { set p_month April }
         "05"    { set p_month May }
         "06"    { set p_month June }
         "07"    { set p_month July }
         "08"    { set p_month August }
         "09"    { set p_month September }
         "10"    { set p_month October }
         "11"    { set p_month November }
         "12"    { set p_month December }
         default { set p_month void }
       }

       switch $day_of_week {
         "0"      { set p_day Sunday }
         "1"      { set p_day Monday }
         "2"      { set p_day Tuesday }
         "3"      { set p_day Wednesday }
         "4"      { set p_day Thursday }
         "5"      { set p_day Friday }
         "6"      { set p_day Saturday }
         default  { set p_day void }
       }

       # check for truncated year patterns
       if { [regsub {yyyy} $pattern $year pattern] == 0 && 
            [regexp {yy}   $pattern] } { 
       
          regsub {yy} $pattern $part_year pattern
       }
       
       # handle all of the standard date/time patterns
       regsub {fmdd}     $pattern $day         pattern
       regsub {dd}       $pattern $day         pattern
       if { [regsub {fmday} $pattern $p_day    pattern] == 0 } {
              regsub {d}  $pattern $day         pattern
       }
       regsub {j}        $pattern $julian_date pattern
       regsub {fmmonth}  $pattern $p_month     pattern
       regsub {mon(th)?} $pattern $month       pattern
       regsub {mm}       $pattern $month       pattern
       regsub {hh24}     $pattern $hour        pattern
       regsub {mi}       $pattern $minute      pattern
       regsub {ss}       $pattern $second      pattern
       regsub {q}        $pattern $qtr         pattern

       return $pattern

' language 'pltcl';

drop function to_char(datetime,char);
create function to_char(datetime,varchar) returns varchar as '
declare 
        indate  alias for $1;
        pattern alias for $2;
        
        doy     varchar;
        j_date  varchar; 
        dow     varchar;
begin
        j_date :=  julian_date(indate);
        doy    :=  day_of_the_year(indate);
        dow    :=  date_part(''dow'',indate);
        return to_char_work(indate,pattern,doy,j_date,dow);
end;
' language 'plpgsql';

-- special case for inserting commas into numbers (e.g. 1,239 from 1239)
create function to_char(integer,varchar) returns varchar as '

       set l  [split $1 ""]
       set nl [list]
       
       # reverse the string
       foreach c $l {
         set nl [linsert $nl 0 $c]
       }

       # convert the string back to its original direction and insert commas
       set l [list]
       set i 0
       foreach c $nl {
         incr i
         set l [linsert $l 0 $c]
         if { [expr $i % 3] == 0 } {set l [linsert $l 0 ","]}
       }

       # return the new string with commas inserted
       return [join $l ""]

' language 'pltcl';

create function to_date_work(varchar,varchar) returns datetime as '
       set      date_in  $1
       set      pattern  [string tolower $2]
       set      month ""

       if ![regexp \-indices {yyyy} $pattern year_long] {

          if [regexp \-indices {yy} $pattern year_short] {
            set year "20[string range $date_in [lindex $year_short 0] [lindex $year_short 1]]"
          } else {
            set year "2000"
          }

       } else {
          set year [string range $date_in [lindex $year_long 0] [lindex $year_long 1]]
       }
       if [regexp \-indices {mm} $pattern month] {
          set month [string range $date_in [lindex $month 0] [lindex $month 1]]
       } else {
         set month "01"
       }
       if [regexp \-indices {dd} $pattern day] {
          set day [string range $date_in [lindex $day 0] [lindex $day 1]]
       } else {
         set day "01"
       }
       if [regexp \-indices {hh} $pattern hour] {
          set hour [string range $date_in [lindex $hour 0] [lindex $hour 1]]
       } else {
         set hour "00"
       }
       if [regexp \-indices {mi} $pattern minute] {
          set minute [string range $date_in [lindex $minute 0] [lindex $minute 1]]
       } else {
         set minute "00"
       }
       if [regexp \-indices {ss} $pattern second] {
          set second [string range $date_in [lindex $second 0] [lindex $second 1]]
       }  else {
         set second "00"
       }     

       return "$year-$month-$day $hour:$minute:$second+00"
       
' language 'pltcl';

-- drop function sanitize_date(varchar);
create function sanitize_date(varchar) returns varchar as '
       
       set date_in [string tolower $1]
       set fancy_months(january)   01
       set fancy_months(february)  02
       set fancy_months(march)     03
       set fancy_months(april)     04
       set fancy_months(may)       05
       set fancy_months(june)      06
       set fancy_months(july)      07
       set fancy_months(august)    08
       set fancy_months(september) 09
       set fancy_months(october)   10
       set fancy_months(november)  11
       set fancy_months(december)  12

       regexp {(january|february|march|april|may|june|july|august|september|october|november|december)} $date_in match

       if [info exists match] {
              set month $fancy_months($match)
	      regsub $match $date_in $month date_in
       }

       return $date_in
' language 'pltcl';


-- drop function to_date(varchar,varchar);
create function to_date(varchar,varchar) returns datetime as '
declare
       date_string      alias for $1;
       pattern          alias for $2;
       clean_string     varchar;
begin
       clean_string := sanitize_date(date_string);
       return to_date_work(clean_string,pattern);
end;
' language 'plpgsql';

-- drop function to_date(datetime,varhchar);
create function to_date(datetime,varchar) returns datetime as '
declare
       date_string      alias for $1;
       pattern          alias for $2;
       clean_string     varchar;
begin
       clean_string := date_string;
       return to_date_work(clean_string,pattern);
end;
' language 'plpgsql';

create function to_date(varchar) returns datetime as '
declare 
        date_string     alias for $1;
begin
        return date_string;
end;
' language 'plpgsql';


create function to_date(integer,varchar) returns datetime as '
declare
                jdate   alias for $1;
                pattern alias for $2;
begin
                return julian_zero_date() + jdate;
end;
' language 'plpgsql';

-- drop function delta_days(integer,varchar);
create function delta_days(integer,varchar) returns varchar as '

       set day_in  $1
       set pattern [string toupper $2]

        switch \-regexp $pattern {
        "SUN(DAY)?"    { set the_day 0 }
        "MON(DAY)?"    { set the_day 1 }
        "TUES(DAY)?"   { set the_day 2 }
        "WED(NESDAY)?" { set the_day 3 }
        "THURS(DAY)?"  { set the_day 4 }
        "FRI(DAY)?"    { set the_day 5 }
        "SAT(URDAY)?"  { set the_day 6 }
        }


       set d_days [expr (($the_day - $day_in) + 7) % 7]
       return "$d_days day"

' language 'pltcl';


-- drop function next_day(datetime,varchar);
create function next_day(datetime,varchar) returns datetime as '
declare
       the_date      alias for $1;
       pattern       alias for $2;
       dow           integer;
begin
       dow    := date_part(''dow'',the_date);
       return the_date + delta_days(dow,pattern)::timespan; 
end;
' language 'plpgsql';