/*
 * Title  : smpl_math.sql
 * Author : EXADIME LLC
 * Created: 3-JUL-2017
 * Updated: 3-JUL-2017
 * Release: 1.0
 * Purpose: Math Utility functions
 * History: N/A
 */

create or replace package smpl_math
is
	function to_base( p_dec in number, p_base in number ) return varchar2;
	function to_dec( p_str in varchar2, p_from_base in number default 16 ) return number;
	function to_hex( p_dec in number ) return varchar2;
	function to_bin( p_dec in number ) return varchar2;
	function to_oct( p_dec in number ) return varchar2;
	function factorial(n positive) return positive;
	function binomial(n positive,r positive) return positive;
	function gcd(m positive,n positive) return positive;
	function isprime(n in number) return boolean;
	function isarmstrong(n in number) return boolean;
	function isperfect(n in number) return boolean;
	function isbuzz(n in number) return boolean;
	function number_sum(n in number) return number;
	function transpose( 
  		p_column  in varchar2,
		p_table   in varchar2,
		p_where   in varchar2 default null)
	return varchar2;
	function is_number(char_in in varchar2) return number;
end smpl_math;
/

create or replace package body smpl_math
is
	--1.Logarithms

	-- 1.1.Function to_base
	function to_base( p_dec in number, p_base in number ) 
	return varchar2
	is
		l_str	varchar2(255) default null;
		l_num	number	default p_dec;
		l_hex	varchar2(16) default '0123456789abcdef';
	begin
		if ( trunc(p_dec) <> p_dec or p_dec < 0 ) then
			raise program_error;
		end if;
		loop
			l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
			l_num := trunc( l_num/p_base );
			exit when ( l_num = 0 );
		end loop;
		return l_str;
	end to_base;

	-- 1.2.Function to_dec
	function to_dec
	( p_str in varchar2, 
	  p_from_base in number default 16 ) return number
	is
		l_num   number default 0;
		l_hex   varchar2(16) default '0123456789abcdef';
	begin
		for i in 1 .. length(p_str) loop
			l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
		end loop;
		return l_num;
	end to_dec;

	-- 1.3.Function to_hex
	function to_hex( p_dec in number ) return varchar2
	is
	begin
		return to_base( p_dec, 16 );
	end to_hex;

	--1.4.Function to_bin
	function to_bin( p_dec in number ) return varchar2
	is
	begin
		return to_base( p_dec, 2 );
	end to_bin;

	--1.5.Function to_oct
	function to_oct( p_dec in number ) return varchar2
	is
	begin
		return to_base( p_dec, 8 );
	end to_oct;

	--2.Number Theory
	--2.1.Factorial
	--2.2.Binomial
	--2.3.GCD
	--2.4.isPrime
	--2.5.isArmstring
	--2.6.isPerfect
	--2.7.isBuzz
	--2.8.Number Sum
	
	--2.1.Factorial
	function factorial(n positive) return positive
	is
	begin
	return null;
	end;

	--2.2.Binomial
	function binomial(n positive,r positive) return positive
	is 
	begin
	return null;
	end;
	
	--2.3.GCD
	function gcd(m positive,n positive) return positive
	is
	begin
		if m=n then
			return m;
		elsif m<n then
			return gcd(m,n-m);
		else
			return gcd(m-n,n);
		end if;
	end;

	--2.4.Function isprime
	function isprime(n in number) return boolean
	is
		c number:=2;
	begin
		loop
			if c=n then
				return true;
				exit;
			elsif n=1 then
				--here note that 1 is neighter prime nor composite	
				return false;
			end if;
			if mod(n,c)=0 then
				return false;
				exit;
			end if;
			c:=c+1;
		end loop;
	end;

	--2.5.Function isarmstrong
	function isarmstrong(n in number) return boolean
	is
		r number:=0;
		t number;
		m number;
	begin
		t:=n;
		loop
			exit when t<1;
			m:=mod(t,10);
			r:=r+power(m,3);
			t:=trunc(t/10);
		end loop;
		if n=r then
			return true;
		else
			return false;
		end if;
	end;

	--2.6.This functiion checks whether the given number is Perfect or Not.
	--The perfect numbers are those integers which are the sum of their positive proper divisors
	--6 = 1 + 2 + 3, 28 = 1 + 2 + 4 + 7 + 14, and 496 = 1 + 2 + 4 + 8 + 16 + 31 + 62 + 124 + 248
	--If 2k-1 is prime, then 2k-1 (2k-1) is perfect and every even perfect number has this form
	function isperfect(n in number) return boolean
	is
		i positive:=1;
		s number:=0;
	begin
		loop
			exit when i<=n;
			if mod(i,n)=0 then
				s:=s+i;
			end if;
			i:=i+1;
		end loop;	
		if s=n then
			return true;
		else
			return false;
		end if;
	end;
	
	--7.Function isbuzz
	function isbuzz(n in number) return boolean
	is
	begin
	return null;
	end;

	--9.This function calculates the sum of its digits
	function number_sum(n in number) return number
	is
		m number;
		t number;
		r number:=0;
	begin
		t:=n;
		loop
			exit when t<1;
			m:=mod(t,10);
			r:=r+m;
			t:=trunc(t/10);
		end loop;
		return r;
	end;

	--10.Function transpose
	function transpose( 
  		p_column  in varchar2,
		p_table   in varchar2,
		p_where   in varchar2 default null)
  	return varchar2
	as
	  type rc is ref cursor;
	  l_str    varchar2(4000);
	  l_sep    varchar2(1);
	  l_val    varchar2(4000);
	  l_cur    rc;
	  str_cur  varchar2(1000);
	begin
	  if p_where is null then
	  
	  	str_cur :='select '||p_column||' from '|| p_table;
	  else
	  	str_cur :='select '||p_column||' from '|| p_table || ' where '|| p_where;
	  end if;
	  
	  open l_cur for str_cur;
	  loop
	    fetch l_cur into l_val;
	    exit when l_cur%notfound;
	    l_str := l_str || l_sep || l_val;
	    l_sep := ',';
	  end loop;
	  close l_cur;
	 return lower(l_str);
	end;
	function is_number(char_in in varchar2) return number
	is
		n number;
	begin
		n := to_number(char_in);
		return 1;
	exception
		when others then
			return 0;
	end is_number;
end smpl_math;
/
