functions

--temp tables
use CommunityAssist

--this creates a temp table that is isolated
--to a single session 
Create table #ZPeople
(
    FirstName nvarchar(255),
 Lastname nvarchar(255)

)

Insert into #ZPeople(Firstname, Lastname)
Select PersonFirstName, PersonLastName
From Person
Where PersonLastName like 'Z%'

Select * From #ZPeople

--this creates a global temporary table
--that crosses sessions
Create table ##ZPeople
(
    FirstName nvarchar(255),
 Lastname nvarchar(255)

)

Insert into ##ZPeople(Firstname, Lastname)
Select PersonFirstName, PersonLastName
From Person
Where PersonLastName like 'Z%'

--functions

Go
--a simple function
create function fx_cube
(@number int) --parameters to be passed in
returns int --return type
As
Begin --start blocl
Declare @cube int --declare a varible
Set @Cube = @number * @number * @number
--return the value
return @Cube
End; --end function block
go
--use function
Select dbo.fx_Cube(2)
go

--get a random seed
Create Function fx_Seed
()
returns int
As
Begin
Declare @Time DateTime = GetDate();
Declare @Mille int = Datepart(MILLISECOND, @Time)
Return @Mille

End
go
Select dbo.fx_Seed()
go
--this function hashes a password
Create function fx_hashPass
(@Seed int, @Password nvarchar(50))
Returns Varbinary(500)
As
Begin
Declare @SeedChar nvarchar(4) = Cast(@seed as nvarchar(4))
Declare @CharToHash nvarchar(54) = @SeedChar+@Password
Declare @Hash varbinary(500)
set @Hash=hashbytes('sha2_512', @CharToHash)
return @Hash
End
go
Select dbo.fx_HashPass(dbo.fx_Seed(), 'P@ssw0rd1')
Select dbo.fx_HashPass(dbo.fx_Seed(), 'P@ssw0rd1')

go
--this sees if the passwords match 
Create function fx_Login
(@userName nvarchar(255) ,@password nvarchar(50))
returns int
As
Begin
Declare @seed int
Declare @DBHash varbinary(500)
Declare @Key int
--select values from the database
--and assign them to the variables
Select @key=PersonKey,@seed = PersonPassKey, @DBHash=PersonUserPassword       from Person
 Where PersonUsername=@UserName
 --concatinate the seed and password
Declare @Concat nvarchar(54)=Cast(@seed as nvarchar(4)) + @Password
Declare @NewHash varbinary(500)= hashbytes('Sha2_512',@Concat)
--see if they match
if Not @DBHash=@NewHash
Begin
--if not then just return 0
Set @key = 0
End
Return @Key
end

--test functions and login
Insert into person(PersonLastName, PersonFirstName, PersonUsername, PersonPlainPassword, Personpasskey, PersonEntryDate, PersonUserPassword)
Values ('Smith', 'Bob', 'bobSmith@gmail.com', 'BobPass',
dbo.fx_Seed(), GetDate(), 
dbo.fx_hashPass(dbo.fx_Seed(), 'BobPass'))

Select * From Person

Select dbo.fx_Login('bobSmith@gmail.com','BobPass')

USE [CommunityAssist]
GO

/****** Object:  UserDefinedFunction [dbo].[fx_MedianAvg]    Script Date: 2/18/2016 11:32:43 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--this function returns a median average
Create Function [dbo].[fx_MedianAvg]
(@Max decimal(16,4), @Min Decimal(16,4))
returns decimal(16,4)
As
Begin
Declare @Range decimal(16,4)
Declare @Median Decimal(16,4)
Select @Range= @Max-@min
Set @Median = @Range /2
Return @Median
End
GO


Select Avg(GrantAllocation) from Servicegrant
Select dbo.fx_MedianAvg(max(GrantAllocation),min(GrantAllocation))
From ServiceGrant