/*
  PG Explorer Sample Database

  Please note : this sample is in no way functional for anything.

  Create the Database
------------------------------------------------------------------------------
    Highlight the SQL and make sure that there
    is no transaction block by enabling the
     "Execute without transaction" on the toolbar.

      Highlighting SQL will execute the highlighted SQL Only.
      Thus this SQL being in a comment will have no influence

       ------------------------------------------------
       Create Database pgexplorer;
       ------------------------------------------------

  Create Language
------------------------------------------------------------------------------
NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB
         Make sure that the path to the file
         "/usr/lib/pgsql/plpgsql.so" is correct
NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB NB

      PLEASE RUN THE FOLLOWING COMMAND ON YOUR SERVER
       ------------------------------------------------
       createlang -L /usr/lib/pgsql plpgsql pgexplorer
       ------------------------------------------------

  The Following Sample Functions were retrieved from www.brasileiro.net
  Thanks for www.brasileiro.net

  PLEASE EXECUTE EACH FUNCTION SEPERATELY BY HIGHLIGHTING IT WITH YOUR MOUSE
*/

/*----------------------------Stand Alown Functions----------------------------*/
CREATE FUNCTION add_one (integer) RETURNS integer AS '
    BEGIN
        RETURN $1 + 1;
    END;
'
LANGUAGE 'plpgsql';

CREATE FUNCTION concat_text (text, text) RETURNS text AS '
    BEGIN
        RETURN $1 || $2;
    END;
'
LANGUAGE 'plpgsql';
/*
Simple functions to add/subtract dates.
Prototypes: DATE_ADD(DATE,INT,PERIOD) DATE_SUB(DATE,INT,PERIOD)
Where PERIOD is one of 'DAYS','MONTHS',or 'YEARS'.
Examples: SELECT DATE_ADD('1999-12-31',1,'DAYS') will produce '2001-01-01' 


-- DATE_ADD, DATE_SUB date manipulation functions          
-- Copyright (C) 2001  Mark Steele (msteele@inet-interactif.com)                                                                   
--                                                                                                                                 
-- This program is free software; you can redistribute it and/or                                                                   
-- modify it under the terms of the GNU General Public License                                                                     
-- as published by the Free Software Foundation; either version 2                                                                  
-- of the License, or (at your option) any later version.                                                                          
--                                                                                                                                 
-- This program 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 General Public License for more details.                                                                                    
--                                                                                                                                 
-- You should have received a copy of the GNU General Public License                                                               
-- along with this program; if not, write to the Free Software                                                                     
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
*/
CREATE FUNCTION date_add(date,int4,text) RETURNS DATE AS '
DECLARE                                                                                                                            
        var1 date;                                                                                                                 
        var2 text;                                                                                                                 
BEGIN                                                                                                                              
        var2 = $2 || '' '' || $3;
        SELECT INTO var1                                                                                                           
                to_date($1 + var2::interval, ''YYYY-MM-DD'');                                                                  
RETURN var1;                                                                                                                       
END;
'
LANGUAGE 'plpgsql';

                                                                                              
CREATE FUNCTION date_sub(date,int4,text) RETURNS DATE AS '
DECLARE                                                                                                                            
        var1 date;                                                                                                                 
        var2 text;
BEGIN                                                                                                                              
        var2 = $2 || '' '' || $3;
        SELECT INTO var1                                                                                                           
                to_date($1 - var2::interval, ''YYYY-MM-DD'');                                                                  
RETURN var1;                                                                                                                       
END;
'
LANGUAGE 'plpgsql';

/*----------------------------     SEQUENCE       ----------------------------*/

CREATE SEQUENCE "emp_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
/*----------------------------     TABLES       ----------------------------*/
CREATE TABLE emp (
    "emp_id" int4 DEFAULT nextval('emp_id_seq')  NOT NULL,
    "empname" varchar(50),
    "salary" int4,
    "last_date" timestamp,
    "last_user" varchar(50),
    "date_entered"  timestamp DEFAULT now() ,
    PRIMARY KEY ("emp_id")

);
CREATE SEQUENCE "employer_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
CREATE TABLE employer (
    "empoyler_id" int4 DEFAULT nextval('employer_id_seq')  NOT NULL,
    "empname" varchar(50),
    "date_entered"  timestamp DEFAULT now() ,
    PRIMARY KEY ("empoyler_id")
);
/* New in Ver 1.01*/
CREATE RULE employer_name_check AS ON INSERT TO employer WHERE (new.empname ISNULL) DO INSTEAD NOTHING;

CREATE TABLE emprelempl (
    "empoyler_id" int4 NOT NULL,
    "emp_id" int4 NOT NULL,
    "date_set"  timestamp DEFAULT now() ,
     PRIMARY KEY("empoyler_id","emp_id")
);

CREATE  INDEX "empname_idx" ON "emp" ("empname");
/*----------------------------     FOREIGN KEY       ----------------------------*/
ALTER TABLE "emprelempl" ADD CONSTRAINT "employer_fk" FOREIGN KEY (empoyler_id) REFERENCES employer(empoyler_id) MATCH FULL;
ALTER TABLE "emprelempl" ADD CONSTRAINT "emplyee_fk" FOREIGN KEY (emp_id) REFERENCES emp(emp_id) MATCH FULL;

COMMENT ON COLUMN "employer"."empoyler_id" IS 'Sequence Key';
COMMENT ON COLUMN "employer"."empname" IS 'Employee Name';
COMMENT ON TABLE "employer" IS 'Employer Table';
COMMENT ON COLUMN "emp"."emp_id" IS 'Sequence Key';
COMMENT ON COLUMN "emp"."salary" IS 'Employee Salary';
COMMENT ON TABLE "emp" IS 'Employee Table';
COMMENT ON TABLE "emprelempl" IS 'Employee Employer Relation';



/*----------------------------     TRIGGERS       ----------------------------*/
/*
 This trigger ensures, that any time a row is inserted or updated in the table,
 the current user name and time are stamped into the row.
 And it ensures that an employees name is given and that the salary is a positive value.
 */
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
    BEGIN
        /* Check that empname and salary are given */
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION ''empname cannot be NULL value'';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
        END IF;

        /* Who works for us when she must pay for? */
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% cannot have a negative salary'', NEW.empname;
        END IF;

        /* Remember who changed the payroll when */
        NEW.last_date := now();
        NEW.last_user := current_user;
        RETURN NEW;
    END;
'
LANGUAGE 'plpgsql';

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();


/*
In this example, we take EMP (a table) and an integer as arguments to our function,
which returns a boolean. If the "salary" field of the EMP table is NULL,
we return "f". Otherwise we compare with that field with the integer passed
to the function and return the boolean result of the comparison (t or f).
*/
CREATE FUNCTION c_overpaid (EMP, integer) RETURNS boolean AS '
    DECLARE
        emprec ALIAS FOR $1;
        sallim ALIAS FOR $2;
    BEGIN
        IF emprec.salary ISNULL THEN
            RETURN ''f'';
        END IF;
        RETURN emprec.salary > sallim;
    END;
'
LANGUAGE 'plpgsql';

/*
This function returns the interval of time for which the two datetime ranges overlap.
They are an expansion of the builtin overlaps(dt1,dt2,dt3,dt4) function.
Contributed by Josh Berkus. 


-- This function returns the interval of time for which the two
-- datetime ranges overlap.
-- By Josh Berkus
*/

CREATE FUNCTION overlap_interval(DATETIME, DATETIME, DATETIME, DATETIME)
RETURNS INTERVAL AS '
DECLARE
    begin1 ALIAS for $1;
    end1 ALIAS for $2;
    begin2 ALIAS for $3;
    end2 ALIAS for $4;
    overlap_amount INTERVAL;
BEGIN
    /* test for overlap using the ovelap function.
       if not found, return 0 interval. */

 
    IF NOT overlaps(begin1, end1, begin2, end2) THEN
        RETURN ''00:00:00''::INTERVAL;
    END IF;
       /* otherwise, test for the various forms of overlap */
 
    IF begin1 < begin2 THEN
        IF end1 < end2 THEN
            overlap_amount := end1 - begin2;
        ELSE
            overlap_amount := end2 - begin2;
        END IF;
    ELSE
        IF end1 < end2 THEN
            overlap_amount := end1 - begin1;
        ELSE
            overlap_amount := end2 - begin1;
        END IF;
    END IF;
 
    RETURN overlap_amount;
END;
'
LANGUAGE 'plpgsql';

/*
Given a book title (string starting with 'The' or 'A', case insensitive),
then it returns the title with the prefix (A or The) move to the end of title.



-- Function to massage book titles.
 -- Examples: Given 'The Book is Mine', 'Book is Mine, The' would be returned.
 --           Given 'A Super Guide', 'Super Guide, A' would be returned
 -- The function is case insensitive and will return the first letter of each word
 -- capitalised.
 -- By Patrick Giagnocavo, heavily modified by Roberto Mello (rmello@fslc.usu.edu)
*/
 CREATE FUNCTION title_words (varchar) returns varchar AS '                                                                                             
 DECLARE
     the_title alias for $1;                                                                                                                        
     temp_title varchar(255);                                                                                                               
     cap_title varchar(255);                                                                                                                
     trimmed_title varchar(255);                                                                                                                    
 BEGIN                                                                                                                                            
     /* Capitalize each word in the title, then grab first 4 letters */
     cap_title := initcap(the_title);                                                                                                     
     temp_title := substring(cap_title from 1 for 4);                                                                                     
                                                                                                                                                        
     if temp_title like ''The %'' then                                                                                                    
         trimmed_title := trim (leading ''The '' from cap_title);                                                                        
         trimmed_title := trimmed_title || '', The'';                                                                            
         return trimmed_title;                                                                                                   
     end if;                                                                                                                              
                                                                                                                                                        
     if temp_title like ''A %'' then                                                                                                      
         trimmed_title := trim (leading ''A '' from cap_title);                                                                          
         trimmed_title := trimmed_title || '', A'';                                                                              
         return trimmed_title;                                                                                                   
     end if;                                                                                                                              
                                                                                                                                                        
     return cap_title ;                                                                                                                             
 END;                                                                                                                                                   
'
language 'plpgsql';

/*
Requirment: To create a function in Postgres that will return nearest ZIP from a source ZIP.
ZIP code table:
*/

CREATE TABLE "zips" (
   "zip" varchar(10) NOT NULL,
   "state" char(2) DEFAULT 'NA' NOT NULL,
   "city" varchar(50) DEFAULT 'NA' NOT NULL,
   "location" point DEFAULT '(0,0)' NOT NULL,
    PRIMARY KEY ("zip")
);
Insert into zips values('35005','AL','ADAMSVILLE','(86.959727,33.588437)');
Insert into zips values('35006','AL','ADGER','(87.167455,33.434277)');
Insert into zips values('35007','AL','KEYSTONE','(86.812861,33.236868)');
Insert into zips values('35010','AL','NEW SITE','(85.951086,32.941445)');
Insert into zips values('35014','AL','ALPINE','(86.208934,33.331165)');
Insert into zips values('35016','AL','ARAB','(86.489638,34.328339)');

/* ZIP code data provided from: http://ftp.census.gov/geo/www/gazetteer/places.html 

Install the Earthdistance from the contrib section of Postgresql.

*/
create function locate_providers (varchar(10),int2) returns text as '
declare
 source_zip alias for $1;
 offset alias for $2;
 source_point point;
 result record;
begin
  /* Get the source point by zip */
  select into source_point location from zips where zip = source_zip;

  if not found
  then return ''Error: ZIP not found'';
  end if;

  /* Get the providers one at a time by offset */
  select into result
    trim(p.name)::text as name, trim(p.address1)::text as address,
    p.phone1::text as phone, trim(p.city)::text as city,
    round(source_point <@> location)::text as distance
  from zips z, providers p
  where source_point <@> location < 50 and z.zip = p.zip
  order by distance
  limit 1, offset;

  /* Return */
  if found
    then return result.name || '', '' || result.address || '', '' ||
                result.city || '', '' || result.phone || '', '' ||
                result.distance || '' Miles'';
  end if;

  /* if nothing found */
  return ''Error: Provider Not Found'';
end;
'
language 'plpgsql';


/*
AUTHOR: Hano de la Rouviere
LICENSE: Free
DESCTIPTION: Convert a numeric amount into words.
USAGE: select amount_to_words(12345.12);
URL : http://www.pgexplorer.com
GUI Tool for Postgres
*/



CREATE FUNCTION units_to_words(int4) RETURNS varchar(20) AS '
DECLARE
    result_str varchar(20);

BEGIN
SELECT INTO result_str
  CASE $1
    WHEN 0 THEN ''''
    WHEN 1 THEN '' ONE''
    WHEN 2 THEN '' TWO''
    WHEN 3 THEN '' THREE''
    WHEN 4 THEN '' FOUR''
    WHEN 5 THEN '' FIVE''
    WHEN 6 THEN '' SIX''
    WHEN 7 THEN '' SEVEN''
    WHEN 8 THEN '' EIGHT''
    WHEN 9 THEN '' NINE''
    WHEN 10 THEN '' TEN''
    WHEN 11 THEN '' ELEVEN''
    WHEN 12 THEN '' TWELVE''
    WHEN 13 THEN '' THIRTEEN''
    WHEN 14 THEN '' FOURTEEN''
    WHEN 15 THEN '' FIFTEEN''
    WHEN 16 THEN '' SIXTEEN''
    WHEN 17 THEN '' SEVENTEEN''
    WHEN 18 THEN '' EIGHTEEN''
    WHEN 19 THEN '' NINETEEN''
END;

RETURN result_str;
END;
'
 LANGUAGE 'plpgsql';


CREATE FUNCTION amount_to_words(numeric(15,2)) RETURNS varchar(255) AS '
DECLARE
       amount ALIAS FOR $1;
       amount_str varchar(30);
       tri int4;
       bi int4;
       mi int4;
       th int4;
       hu int4;
       de int4;
       return_str varchar(255);
BEGIN
amount_str = to_char(amount, ''000000000000D00'');
tri = substr(amount_str, 0, 3)::int4;
bi = substr(amount_str, 2, 3)::int4;
mi = substr(amount_str, 5, 3)::int4;
th = substr(amount_str, 8, 3)::int4;
hu = substr(amount_str, 11, 3)::int4;
de = substr(amount_str, 15, 2)::int4;

if tri > 0 then
       return_str = num_to_words(tri) || '' TRILLION '';
       else
       return_str = '''';
END IF;

if bi > 0 then
       return_str = return_str || num_to_words(bi) || '' BILLION '';
END IF;
if mi > 0 then
       return_str = return_str || num_to_words(mi) || '' MILLION '';
END IF;
if th > 0 then
       return_str = return_str || num_to_words(th) || '' THOUSAND '';
END IF;
if hu > 0 then
   return_str =  return_str || num_to_words(hu);
END IF;
IF return_str != '''' then
   return_str = return_str ||'' DOLLARS AND '';
   else
       return_str = ''ZERO DOLLARS AND '';
END IF;

if de > 0 then
   return_str = return_str || num_to_words(de);
END IF;
return_str = return_str || '' CENTS'';

RETURN return_str;
END;
'
 LANGUAGE 'plpgsql';
