/*    This SQL DDL script was generated by Microsoft Visual Studio (Release Date: LOCAL BUILD). */

/*    Driver Used : Microsoft Visual Studio - Microsoft SQL Server Driver.                    */
/*    Document    : C:\Documents and Settings\Ryan Corrigal\My Documents\Computer Systems Technology\IST410\Database\StAlbertFoodBank.vsd. */
/*    Time Created: March 11, 20508 2:58 PM.                                                   */
/*    Operation   : From Visio Generate Wizard.                                               */
/*    Connected data source : No connection.                                                  */
/*    Connected server      : No connection.                                                  */
/*    Connected database    : Not applicable.                                                 */



SET QUOTED_IDENTIFIER ON

go


/* Create StAlbertFoodBank database.                                                          */
use master  

go

drop database "StAlbertFoodBank"  

go

create database "StAlbertFoodBank"  

go

use "StAlbertFoodBank"  

go

/* Create new table "ClientTypeInformation".                                                  */
/* "ClientTypeInformation" : Table of ClientTypeInformation                                   */
/* 	"ClientTypeInformationId" : ClientTypeInformationId identifies ClientTypeInformation      */
/* 	"ClientId" : ClientId partly identifies ClientTypeInformation                             */
/* 	"ClientTypeId" : ClientTypeId is of ClientTypeInformation                                 */  
create table "ClientTypeInformation" ( 
	"ClientTypeInformationId" int identity(1,1) not null,
	"ClientId" int not null,
	"ClientTypeId" int not null)  

go

alter table "ClientTypeInformation"
	add constraint "ClientTypeInformation_PK" primary key ("ClientTypeInformationId")   


go

/* Create new table "ClientTypes".                                                            */
/* "ClientTypes" : Table of ClientTypes                                                       */
/* 	"ClientTypeId" : ClientTypeId identifies ClientTypes                                      */
/* 	"Description" : Description is of ClientTypes                                             */  
create table "ClientTypes" ( 
	"ClientTypeId" int identity(1,1) not null,
	"Description" varchar(30) null)  

go

alter table "ClientTypes"
	add constraint "ClientTypes_PK" primary key ("ClientTypeId")   


go

/* Create new table "Donees".                                                                 */
/* "Donees" : Table of Donees                                                                 */
/* 	"DoneeId" : DoneeId identifies Donees                                                     */
/* 	"DoneeName" : DoneeName is of Donees                                                      */  
create table "Donees" ( 
	"DoneeId" int identity(1,1) not null,
	"DoneeName" varchar(30) null)  

go

alter table "Donees"
	add constraint "Donees_PK" primary key ("DoneeId")   


go

/* Create new table "OutgoingDonations".                                                      */
/* "OutgoingDonations" : Table of OutgoingDonations                                           */
/* 	"OutgoingDonationId" : OutgoingDonationId identifies OutgoingDonations                    */
/* 	"OutgoingDonationDate" : OutgoingDonationDate is of OutgoingDonations                     */
/* 	"WeightDonated" : WeightDonated is of OutgoingDonations                                   */
/* 	"DoneeId" : DoneeId is of OutgoingDonations                                               */
/* 	"MoneyDonated" : MoneyDonated is of OutgoingDonations                                     */  
create table "OutgoingDonations" ( 
	"OutgoingDonationId" int identity(1,1) not null,
	"OutgoingDonationDate" smalldatetime null,
	"WeightDonated" decimal(11,2) null,
	"DoneeId" int null,
	"MoneyDonated" decimal(11,2) null)  

go

alter table "OutgoingDonations"
	add constraint "OutgoingDonations_PK" primary key ("OutgoingDonationId")   


go

/* Create new table "ReferralAgencyCategories".                                               */
/* "ReferralAgencyCategories" : Table of ReferralAgencyCategories                             */
/* 	"ReferralAgencyCategoryId" : ReferralAgencyCategoryId identifies ReferralAgencyCategories */
/* 	"Description" : Description is of ReferralAgencyCategories                                */  
create table "ReferralAgencyCategories" ( 
	"ReferralAgencyCategoryId" int identity(1,1) not null,
	"Description" varchar(30) not null)  

go

alter table "ReferralAgencyCategories"
	add constraint "ReferralAgencyCategories_PK" primary key ("ReferralAgencyCategoryId")   


go

/* Create new table "VolunteerTypes".                                                         */
/* "VolunteerTypes" : Table of VolunteerTypes                                                 */  
create table "VolunteerTypes" ( 
	"VolunteerTypeId" int identity(1,1) not null,
	"Description" varchar(255) not null) ON 'PRIMARY'  

go

alter table "VolunteerTypes"
	add constraint "VolunteerTypes_PK" primary key clustered ("VolunteerTypeId")   


go

/* Create new table "Volunteers".                                                             */
/* "Volunteers" : Table of Volunteers                                                         */  
create table "Volunteers" ( 
	"VolunteerId" int identity(1,1) not null,
	"LocationId" int not null,
	"VolunteerTypeId" int null,
	"Gender" char(1) not null,
	"FirstName" varchar(50) not null,
	"LastName" varchar(50) not null,
	"MiddleName" varchar(50) null,
	"Address" varchar(255) not null,
	"Phone1" char(10) not null,
	"Phone2" char(10) null,
	"Birthdate" smalldatetime not null,
	"StartDate" smalldatetime not null,
	"EmailAddress" varchar(255) null,
	"PostalCode" char(6) not null,
	"EmergencyContactName" varchar(255) not null,
	"EmergencyContactPhone" char(10) not null) ON 'PRIMARY'  

go

alter table "Volunteers"
	add constraint "Volunteers_PK" primary key clustered ("VolunteerId")   


go

/* Create new table "VolunteerHours".                                                         */
/* "VolunteerHours" : Table of VolunteerHours                                                 */  
create table "VolunteerHours" ( 
	"VolunteerHourId" int identity(1,1) not null,
	"VolunteerId" int not null,
	"Date" smalldatetime not null,
	"HoursWorked" int not null) ON 'PRIMARY'  

go

alter table "VolunteerHours"
	add constraint "VolunteerHours_PK" primary key clustered ("VolunteerHourId")   


go

/* Create new table "TotalAssets".                                                            */
/* "TotalAssets" : Table of TotalAssets                                                       */  
create table "TotalAssets" ( 
	"TotalAssetId" int not null,
	"TotalFood" decimal(11,2) not null,
	"TotalMoney" decimal(11,2) not null) ON 'PRIMARY'  

go

alter table "TotalAssets"
	add constraint "TotalAssets_PK" primary key clustered ("TotalAssetId")   


go

/* Create new table "Referrals".                                                              */
/* "Referrals" : Table of Referrals                                                           */  
create table "Referrals" ( 
	"ReferralId" int identity(1,1) not null,
	"ReferralAgencyId" int not null,
	"ReferralDetails" varchar(255) null,
	"NumberOfAdults" smallint not null,
	"NumberOfChildren" smallint null,
	"ReferralDate" smalldatetime not null,
	"ClientName" varchar(255) null) ON 'PRIMARY'  

go

alter table "Referrals"
	add constraint "Referrals_PK" primary key clustered ("ReferralId")   


go

/* Create new table "ReferralAgencies".                                                       */
/* "ReferralAgencies" : Table of ReferralAgencies                                             */
/* 	"ReferralAgencyCategoryId" : ReferralAgencyCategoryId is of ReferralAgencies              */  
create table "ReferralAgencies" ( 
	"ReferralAgencyId" int identity(1,1) not null,
	"LocationId" int null,
	"Name" varchar(255) not null,
	"Phone1" char(10) null,
	"Phone2" char(10) null,
	"Address" varchar(255) null,
	"Contact" varchar(255) null,
	"EmailAddress" varchar(255) null,
	"ReferralAgencyCategoryId" int null) ON 'PRIMARY'  

go

alter table "ReferralAgencies"
	add constraint "ReferralAgencies_PK" primary key clustered ("ReferralAgencyId")   


go

/* Create new table "Locations".                                                              */
/* "Locations" : Table of Locations                                                           */  
create table "Locations" ( 
	"LocationId" int identity(1,1) not null,
	"CityTown" varchar(50) not null,
	"Province" varchar(50) not null,
	"Country" varchar(50) not null,
	"JurisdictionDescription" varchar(255) null) ON 'PRIMARY'  

go

alter table "Locations"
	add constraint "Locations_PK" primary key clustered ("LocationId")   


go

/* Create new table "Households".                                                             */
/* "Households" : Table of Households                                                         */  
create table "Households" ( 
	"HouseholdId" int identity(1,1) not null,
	"PostalCode" varchar(6) not null,
	"Address" varchar(255) not null,
	"CommunityId" int not null) ON 'PRIMARY'  

go

alter table "Households"
	add constraint "Households_PK" primary key clustered ("HouseholdId")   


go

/* Create new table "FoodHamperStates".                                                       */
/* "FoodHamperStates" : Table of FoodHamperStates                                             */  
create table "FoodHamperStates" ( 
	"StateId" int not null,
	"Description" varchar(255) null) ON 'PRIMARY'  

go

alter table "FoodHamperStates"
	add constraint "FoodHamperStates_PK" primary key clustered ("StateId")   


go

/* Create new table "FoodHampers".                                                            */
/* "FoodHampers" : Table of FoodHampers                                                       */  
create table "FoodHampers" ( 
	"FoodHamperId" int identity(1,1) not null,
	"Weight" decimal(11,2) not null,
	"PickupDate" smalldatetime null,
	"PickupPerson" varchar(255) null,
	"ReferralId" int null,
	"FamilyId" int null) ON 'PRIMARY'  

go

alter table "FoodHampers"
	add constraint "FoodHampers_PK" primary key clustered ("FoodHamperId")   


go

/* Create new table "FamilyStates".                                                           */
/* "FamilyStates" : Table of FamilyStates                                                     */  
create table "FamilyStates" ( 
	"FamilyStateId" int identity(1,1) not null,
	"Description" varchar(255) null) ON 'PRIMARY'  

go

alter table "FamilyStates"
	add constraint "FamilyStates_PK" primary key clustered ("FamilyStateId")   


go

/* Create new table "Families".                                                               */
/* "Families" : Table of Families                                                             */  
create table "Families" ( 
	"FamilyId" int identity(1,1) not null,
	"HeadOfFamilyClientId" int null,
	"FamilyComment" varchar(255) null,
	"HouseholdId" int null,
	"FamilyStateId" int not null,
	"RedFlag" bit not null,
	"Phone1" char(10) null,
	"Phone2" char(10) null) ON 'PRIMARY'  

go

alter table "Families"
	add constraint "Families_PK" primary key clustered ("FamilyId")   


go

/* Create new table "DonorTypes".                                                             */
/* "DonorTypes" : Table of DonorTypes                                                         */  
create table "DonorTypes" ( 
	"DonorTypeId" int identity(1,1) not null,
	"Description" varchar(255) null) ON 'PRIMARY'  

go

alter table "DonorTypes"
	add constraint "DonorTypes_PK" primary key clustered ("DonorTypeId")   


go

/* Create new table "Donors".                                                                 */
/* "Donors" : Table of Donors                                                                 */  
create table "Donors" ( 
	"DonorId" int identity(1,1) not null,
	"LocationId" int null,
	"TypeId" int null,
	"Name" varchar(255) null,
	"Phone1" char(10) null,
	"Phone2" char(10) null,
	"Address" varchar(255) null,
	"EmailAddress" varchar(255) null,
	"Contact" varchar(255) null) ON 'PRIMARY'  

go

alter table "Donors"
	add constraint "Donors_PK" primary key clustered ("DonorId")   


go

/* Create new table "Donations".                                                              */
/* "Donations" : Table of Donations                                                           */  
create table "Donations" ( 
	"DonationId" int identity(1,1) not null,
	"DonorId" int not null,
	"DonationDate" smalldatetime not null,
	"WeightDonated" decimal(11,2) null,
	"MoneyDonated" decimal(11,2) null) ON 'PRIMARY'  

go

alter table "Donations"
	add constraint "Donations_PK" primary key clustered ("DonationId")   


go

/* Create new table "Communities".                                                            */
/* "Communities" : Table of Communities                                                       */  
create table "Communities" ( 
	"CommunityId" int identity(1,1) not null,
	"LocationId" int not null,
	"Name" varchar(255) not null,
	"JurisdictionDescription" varchar(255) null) ON 'PRIMARY'  

go

alter table "Communities"
	add constraint "Communities_PK" primary key clustered ("CommunityId")   


go

/* Create new table "Clients".                                                                */
/* "Clients" : Table of Clients                                                               */  
create table "Clients" ( 
	"ClientId" int identity(1,1) not null,
	"HealthInfo" varchar(255) null,
	"Gender" char(1) not null,
	"FirstName" varchar(50) not null,
	"LastName" varchar(50) not null,
	"MiddleName" varchar(50) null,
	"Birthdate" smalldatetime not null,
	"EmailAddress" varchar(255) null,
	"AhcNumber" varchar(255) not null,
	"FamilyId" int not null) ON 'PRIMARY'  

go

alter table "Clients"
	add constraint "Clients_PK" primary key clustered ("ClientId")   


go

/* Create new table "ClientHouseholdHistory".                                                 */
/* "ClientHouseholdHistory" : Table of ClientHouseholdHistory                                 */  
create table "ClientHouseholdHistory" ( 
	"ClientHouseholdHistoryId" int identity(1,1) not null,
	"ClientId" int not null,
	"HouseholdId" int null,
	"DateMovedOut" char(10) null) ON 'PRIMARY'  

go

alter table "ClientHouseholdHistory"
	add constraint "ClientHouseholdHistory_PK" primary key clustered ("ClientHouseholdHistoryId")   


go

/* Add foreign key constraints to table "ClientTypeInformation".                              */
alter table "ClientTypeInformation"
	add constraint "Clients_ClientTypeInformation_FK1" foreign key (
		"ClientId")
	 references "Clients" (
		"ClientId") on update no action on delete no action  

go

alter table "ClientTypeInformation"
	add constraint "ClientTypes_ClientTypeInformation_FK1" foreign key (
		"ClientTypeId")
	 references "ClientTypes" (
		"ClientTypeId") on update no action on delete no action  

go

/* Add foreign key constraints to table "OutgoingDonations".                                  */
alter table "OutgoingDonations"
	add constraint "Donees_OutgoingDonations_FK1" foreign key (
		"DoneeId")
	 references "Donees" (
		"DoneeId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Volunteers".                                         */
alter table "Volunteers"
	add constraint "Locations_Volunteers_FK1" foreign key (
		"LocationId")
	 references "Locations" (
		"LocationId") on update no action on delete no action  

go

alter table "Volunteers"
	add constraint "VolunteerTypes_Volunteers_FK1" foreign key (
		"VolunteerTypeId")
	 references "VolunteerTypes" (
		"VolunteerTypeId") on update no action on delete no action  

go

/* Add foreign key constraints to table "VolunteerHours".                                     */
alter table "VolunteerHours"
	add constraint "Volunteers_VolunteerHours_FK1" foreign key (
		"VolunteerId")
	 references "Volunteers" (
		"VolunteerId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Referrals".                                          */
alter table "Referrals"
	add constraint "ReferralAgencies_Referrals_FK1" foreign key (
		"ReferralAgencyId")
	 references "ReferralAgencies" (
		"ReferralAgencyId") on update no action on delete no action  

go

/* Add foreign key constraints to table "ReferralAgencies".                                   */
alter table "ReferralAgencies"
	add constraint "Locations_ReferralAgencies_FK1" foreign key (
		"LocationId")
	 references "Locations" (
		"LocationId") on update no action on delete no action  

go

alter table "ReferralAgencies"
	add constraint "ReferralAgencyCategories_ReferralAgencies_FK1" foreign key (
		"ReferralAgencyCategoryId")
	 references "ReferralAgencyCategories" (
		"ReferralAgencyCategoryId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Households".                                         */
alter table "Households"
	add constraint "Communities_Households_FK1" foreign key (
		"CommunityId")
	 references "Communities" (
		"CommunityId") on update no action on delete no action  

go

/* Add foreign key constraints to table "FoodHampers".                                        */
alter table "FoodHampers"
	add constraint "Families_FoodHampers_FK1" foreign key (
		"FamilyId")
	 references "Families" (
		"FamilyId") on update no action on delete no action  

go

alter table "FoodHampers"
	add constraint "Referrals_FoodHampers_FK1" foreign key (
		"ReferralId")
	 references "Referrals" (
		"ReferralId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Families".                                           */
alter table "Families"
	add constraint "Clients_Families_FK1" foreign key (
		"HeadOfFamilyClientId")
	 references "Clients" (
		"ClientId") on update no action on delete no action  

go

alter table "Families"
	add constraint "FamilyStates_Families_FK1" foreign key (
		"FamilyStateId")
	 references "FamilyStates" (
		"FamilyStateId") on update no action on delete no action  

go

alter table "Families"
	add constraint "Households_Families_FK1" foreign key (
		"HouseholdId")
	 references "Households" (
		"HouseholdId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Donors".                                             */
alter table "Donors"
	add constraint "DonorTypes_Donors_FK1" foreign key (
		"TypeId")
	 references "DonorTypes" (
		"DonorTypeId") on update no action on delete no action  

go

alter table "Donors"
	add constraint "Locations_Donors_FK1" foreign key (
		"LocationId")
	 references "Locations" (
		"LocationId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Donations".                                          */
alter table "Donations"
	add constraint "Donors_Donations_FK1" foreign key (
		"DonorId")
	 references "Donors" (
		"DonorId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Communities".                                        */
alter table "Communities"
	add constraint "Locations_Communities_FK1" foreign key (
		"LocationId")
	 references "Locations" (
		"LocationId") on update no action on delete no action  

go

/* Add foreign key constraints to table "Clients".                                            */
alter table "Clients"
	add constraint "Families_Clients_FK1" foreign key (
		"FamilyId")
	 references "Families" (
		"FamilyId") on update no action on delete no action  

go

/* Add foreign key constraints to table "ClientHouseholdHistory".                             */
alter table "ClientHouseholdHistory"
	add constraint "Clients_ClientHouseholdHistory_FK1" foreign key (
		"ClientId")
	 references "Clients" (
		"ClientId") on update no action on delete no action  

go

alter table "ClientHouseholdHistory"
	add constraint "Households_ClientHouseholdHistory_FK1" foreign key (
		"HouseholdId")
	 references "Households" (
		"HouseholdId") on update no action on delete no action  

go


/* This is the end of the Microsoft Visual Studio generated SQL DDL script.                   */

create procedure AddClientHouseholdHistory
(
@ClientId int,
@HouseholdId int,
@DateMovedOut char
)
as
--Trim Strings

--Please Remove the last comma above

--Set Nulls

insert into ClientHouseholdHistory
(ClientId, HouseholdId, DateMovedOut)
values
(@ClientId, @HouseholdId, @DateMovedOut)

select @@identity
return

go

create procedure DeleteClientHouseholdHistoryById
(@ClientHouseholdHistoryId int)
as

delete from ClientHouseholdHistory
where ClientHouseholdHistoryId = @ClientHouseholdHistoryId

return

go

create procedure GetClientHouseholdHistory
as

select	ClientHouseholdHistoryId,
	ClientId,
	HouseholdId,
	DateMovedOut
from ClientHouseholdHistory

return

go

create procedure GetClientHouseholdHistoryById
(@ClientHouseholdHistoryId int)
as

select	ClientHouseholdHistoryId,
	ClientId,
	HouseholdId,
	DateMovedOut
from ClientHouseholdHistory
where ClientHouseholdHistoryId = @ClientHouseholdHistoryId

return

go

create procedure UpdateClientHouseholdHistory
(
@ClientHouseholdHistoryId int,
@ClientId int,
@HouseholdId int,
@DateMovedOut char
)
as
--Trim Strings

--Set Nulls

update ClientHouseholdHistory
set	ClientId = @ClientId,
	HouseholdId = @HouseholdId,
	DateMovedOut = @DateMovedOut
where ClientHouseholdHistoryId = @ClientHouseholdHistoryId

return

go


create procedure AddClient
(
@HealthInfo varchar(255),
@Gender char,
@FirstName varchar(255),
@LastName varchar(255),
@MiddleName varchar(255),
@Birthdate smalldatetime,
@EmailAddress varchar(255),
@AhcNumber varchar(255),
@FamilyId int
)
as
--Trim Strings
SET @HealthInfo = ltrim(rtrim(@HealthInfo))
SET @FirstName = ltrim(rtrim(@FirstName))
SET @LastName = ltrim(rtrim(@LastName))
SET @MiddleName = ltrim(rtrim(@MiddleName))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))
SET @AhcNumber = ltrim(rtrim(@AhcNumber))

--Please Remove the last comma above

--Set Nulls
if @HealthInfo = ''
	 set @HealthInfo = NULL
if @FirstName = ''
	 set @FirstName = NULL
if @LastName = ''
	 set @LastName = NULL
if @MiddleName = ''
	 set @MiddleName = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL
if @AhcNumber = ''
	 set @AhcNumber = NULL

insert into Clients
(HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId)
values
(@HealthInfo, @Gender, @FirstName, @LastName, @MiddleName, @Birthdate, @EmailAddress, @AhcNumber, @FamilyId)

select @@identity
return

go

create procedure DeleteClientById
(@ClientId int)
as

delete from Clients
where ClientId = @ClientId

return

go

create procedure GetClients
as

select	ClientId,
	HealthInfo,
	Gender,
	FirstName,
	LastName,
	MiddleName,
	Birthdate,
	EmailAddress,
	AhcNumber,
	FamilyId
from Clients

return

go

create procedure GetClientById
(@ClientId int)
as

select	ClientId,
	HealthInfo,
	Gender,
	FirstName,
	LastName,
	MiddleName,
	Birthdate,
	EmailAddress,
	AhcNumber,
	FamilyId
from Clients
where ClientId = @ClientId

return

go

create procedure UpdateClient
(
@ClientId int,
@HealthInfo varchar(255),
@Gender char,
@FirstName varchar(255),
@LastName varchar(255),
@MiddleName varchar(255),
@Birthdate smalldatetime,
@EmailAddress varchar(255),
@AhcNumber varchar(255),
@FamilyId int
)
as
--Trim Strings
SET @HealthInfo = ltrim(rtrim(@HealthInfo))
SET @FirstName = ltrim(rtrim(@FirstName))
SET @LastName = ltrim(rtrim(@LastName))
SET @MiddleName = ltrim(rtrim(@MiddleName))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))
SET @AhcNumber = ltrim(rtrim(@AhcNumber))

--Set Nulls
if @HealthInfo = ''
	 set @HealthInfo = NULL
if @FirstName = ''
	 set @FirstName = NULL
if @LastName = ''
	 set @LastName = NULL
if @MiddleName = ''
	 set @MiddleName = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL
if @AhcNumber = ''
	 set @AhcNumber = NULL

update Clients
set	HealthInfo = @HealthInfo,
	Gender = @Gender,
	FirstName = @FirstName,
	LastName = @LastName,
	MiddleName = @MiddleName,
	Birthdate = @Birthdate,
	EmailAddress = @EmailAddress,
	AhcNumber = @AhcNumber,
	FamilyId = @FamilyId
where ClientId = @ClientId

return

go


create procedure AddClientTypeInformation
(
@ClientId int,
@ClientTypeId int
)
as
--Trim Strings

--Please Remove the last comma above

--Set Nulls

insert into ClientTypeInformation
(ClientId, ClientTypeId)
values
(@ClientId, @ClientTypeId)

select @@identity
return

go

create procedure DeleteClientTypeInformationById
(@ClientTypeInformationId int)
as

delete from ClientTypeInformation
where ClientTypeInformationId = @ClientTypeInformationId

return

go

create procedure GetClientTypeInformation
as

select	ClientTypeInformationId,
	ClientId,
	ClientTypeId
from ClientTypeInformation

return

go

create procedure GetClientTypeInformationById
(@ClientTypeInformationId int)
as

select	ClientTypeInformationId,
	ClientId,
	ClientTypeId
from ClientTypeInformation
where ClientTypeInformationId = @ClientTypeInformationId

return

go

create procedure UpdateClientTypeInformation
(
@ClientTypeInformationId int,
@ClientId int,
@ClientTypeId int
)
as
--Trim Strings

--Set Nulls

update ClientTypeInformation
set	ClientId = @ClientId,
	ClientTypeId = @ClientTypeId
where ClientTypeInformationId = @ClientTypeInformationId

return

go


create procedure AddClientType
(
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Please Remove the last comma above

--Set Nulls
if @Description = ''
	 set @Description = NULL

insert into ClientTypes
(Description)
values
(@Description)

select @@identity
return

go

create procedure DeleteClientTypeById
(@ClientTypeId int)
as

delete from ClientTypes
where ClientTypeId = @ClientTypeId

return

go

create procedure GetClientTypes
as

select	ClientTypeId,
	Description
from ClientTypes

return

go

create procedure GetClientTypeById
(@ClientTypeId int)
as

select	ClientTypeId,
	Description
from ClientTypes
where ClientTypeId = @ClientTypeId

return

go

create procedure UpdateClientType
(
@ClientTypeId int,
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Set Nulls
if @Description = ''
	 set @Description = NULL

update ClientTypes
set	Description = @Description
where ClientTypeId = @ClientTypeId

return

go


create procedure AddCommunity
(
@LocationId int,
@Name varchar(255),
@JurisdictionDescription varchar(255)
)
as
--Trim Strings
SET @Name = ltrim(rtrim(@Name))
SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))

--Please Remove the last comma above

--Set Nulls
if @Name = ''
	 set @Name = NULL
if @JurisdictionDescription = ''
	 set @JurisdictionDescription = NULL

insert into Communities
(LocationId, Name, JurisdictionDescription)
values
(@LocationId, @Name, @JurisdictionDescription)

select @@identity
return

go

create procedure DeleteCommunityById
(@CommunityId int)
as

delete from Communities
where CommunityId = @CommunityId

return

go

create procedure GetCommunities
as

select	CommunityId,
	LocationId,
	Name,
	JurisdictionDescription
from Communities

return

go

create procedure GetCommunityById
(@CommunityId int)
as

select	CommunityId,
	LocationId,
	Name,
	JurisdictionDescription
from Communities
where CommunityId = @CommunityId

return

go

create procedure UpdateCommunity
(
@CommunityId int,
@LocationId int,
@Name varchar(255),
@JurisdictionDescription varchar(255)
)
as
--Trim Strings
SET @Name = ltrim(rtrim(@Name))
SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))

--Set Nulls
if @Name = ''
	 set @Name = NULL
if @JurisdictionDescription = ''
	 set @JurisdictionDescription = NULL

update Communities
set	LocationId = @LocationId,
	Name = @Name,
	JurisdictionDescription = @JurisdictionDescription
where CommunityId = @CommunityId

return

go


create procedure AddDonation
(
@DonorId int,
@DonationDate smalldatetime,
@WeightDonated decimal,
@MoneyDonated decimal
)
as
--Trim Strings

--Please Remove the last comma above

--Set Nulls

insert into Donations
(DonorId, DonationDate, WeightDonated, MoneyDonated)
values
(@DonorId, @DonationDate, @WeightDonated, @MoneyDonated)

select @@identity
return

go

create procedure DeleteDonationById
(@DonationId int)
as

delete from Donations
where DonationId = @DonationId

return

go

create procedure GetDonations
as

select	DonationId,
	DonorId,
	DonationDate,
	WeightDonated,
	MoneyDonated
from Donations

return

go

create procedure GetDonationById
(@DonationId int)
as

select	DonationId,
	DonorId,
	DonationDate,
	WeightDonated,
	MoneyDonated
from Donations
where DonationId = @DonationId

return

go

create procedure UpdateDonation
(
@DonationId int,
@DonorId int,
@DonationDate smalldatetime,
@WeightDonated decimal,
@MoneyDonated decimal
)
as
--Trim Strings

--Set Nulls

update Donations
set	DonorId = @DonorId,
	DonationDate = @DonationDate,
	WeightDonated = @WeightDonated,
	MoneyDonated = @MoneyDonated
where DonationId = @DonationId

return

go


create procedure AddDonee
(
@DoneeName varchar(255)
)
as
--Trim Strings
SET @DoneeName = ltrim(rtrim(@DoneeName))

--Please Remove the last comma above

--Set Nulls
if @DoneeName = ''
	 set @DoneeName = NULL

insert into Donees
(DoneeName)
values
(@DoneeName)

select @@identity
return

go

create procedure DeleteDoneeById
(@DoneeId int)
as

delete from Donees
where DoneeId = @DoneeId

return

go

create procedure GetDonees
as

select	DoneeId,
	DoneeName
from Donees

return

go

create procedure GetDoneeById
(@DoneeId int)
as

select	DoneeId,
	DoneeName
from Donees
where DoneeId = @DoneeId

return

go

create procedure UpdateDonee
(
@DoneeId int,
@DoneeName varchar(255)
)
as
--Trim Strings
SET @DoneeName = ltrim(rtrim(@DoneeName))

--Set Nulls
if @DoneeName = ''
	 set @DoneeName = NULL

update Donees
set	DoneeName = @DoneeName
where DoneeId = @DoneeId

return

go


create procedure AddDonor
(
@LocationId int,
@TypeId int,
@Name varchar(255),
@Phone1 char,
@Phone2 char,
@Address varchar(255),
@EmailAddress varchar(255),
@Contact varchar(255)
)
as
--Trim Strings
SET @Name = ltrim(rtrim(@Name))
SET @Address = ltrim(rtrim(@Address))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))
SET @Contact = ltrim(rtrim(@Contact))

--Please Remove the last comma above

--Set Nulls
if @Name = ''
	 set @Name = NULL
if @Address = ''
	 set @Address = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL
if @Contact = ''
	 set @Contact = NULL

insert into Donors
(LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact)
values
(@LocationId, @TypeId, @Name, @Phone1, @Phone2, @Address, @EmailAddress, @Contact)

select @@identity
return

go

create procedure DeleteDonorById
(@DonorId int)
as

delete from Donors
where DonorId = @DonorId

return

go

create procedure GetDonors
as

select	DonorId,
	LocationId,
	TypeId,
	Name,
	Phone1,
	Phone2,
	Address,
	EmailAddress,
	Contact
from Donors

return

go

create procedure GetDonorById
(@DonorId int)
as

select	DonorId,
	LocationId,
	TypeId,
	Name,
	Phone1,
	Phone2,
	Address,
	EmailAddress,
	Contact
from Donors
where DonorId = @DonorId

return

go

create procedure UpdateDonor
(
@DonorId int,
@LocationId int,
@TypeId int,
@Name varchar(255),
@Phone1 char,
@Phone2 char,
@Address varchar(255),
@EmailAddress varchar(255),
@Contact varchar(255)
)
as
--Trim Strings
SET @Name = ltrim(rtrim(@Name))
SET @Address = ltrim(rtrim(@Address))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))
SET @Contact = ltrim(rtrim(@Contact))

--Set Nulls
if @Name = ''
	 set @Name = NULL
if @Address = ''
	 set @Address = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL
if @Contact = ''
	 set @Contact = NULL

update Donors
set	LocationId = @LocationId,
	TypeId = @TypeId,
	Name = @Name,
	Phone1 = @Phone1,
	Phone2 = @Phone2,
	Address = @Address,
	EmailAddress = @EmailAddress,
	Contact = @Contact
where DonorId = @DonorId

return

go


create procedure AddDonorType
(
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Please Remove the last comma above

--Set Nulls
if @Description = ''
	 set @Description = NULL

insert into DonorTypes
(Description)
values
(@Description)

select @@identity
return

go

create procedure DeleteDonorTypeById
(@DonorTypeId int)
as

delete from DonorTypes
where DonorTypeId = @DonorTypeId

return

go

create procedure GetDonorTypes
as

select	DonorTypeId,
	Description
from DonorTypes

return

go

create procedure GetDonorTypeById
(@DonorTypeId int)
as

select	DonorTypeId,
	Description
from DonorTypes
where DonorTypeId = @DonorTypeId

return

go

create procedure UpdateDonorType
(
@DonorTypeId int,
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Set Nulls
if @Description = ''
	 set @Description = NULL

update DonorTypes
set	Description = @Description
where DonorTypeId = @DonorTypeId

return

go


create procedure AddFamily
(
@HeadOfFamilyClientId int,
@FamilyComment varchar(255),
@HouseholdId int,
@FamilyStateId int,
@RedFlag bit,
@Phone1 char,
@Phone2 char
)
as
--Trim Strings
SET @FamilyComment = ltrim(rtrim(@FamilyComment))

--Please Remove the last comma above

--Set Nulls
if @FamilyComment = ''
	 set @FamilyComment = NULL

insert into Families
(HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2)
values
(@HeadOfFamilyClientId, @FamilyComment, @HouseholdId, @FamilyStateId, @RedFlag, @Phone1, @Phone2)

select @@identity
return

go

create procedure DeleteFamilyById
(@FamilyId int)
as

delete from Families
where FamilyId = @FamilyId

return

go

create procedure GetFamilies
as

select	FamilyId,
	HeadOfFamilyClientId,
	FamilyComment,
	HouseholdId,
	FamilyStateId,
	RedFlag,
	Phone1,
	Phone2
from Families

return

go

create procedure GetFamilyById
(@FamilyId int)
as

select	FamilyId,
	HeadOfFamilyClientId,
	FamilyComment,
	HouseholdId,
	FamilyStateId,
	RedFlag,
	Phone1,
	Phone2
from Families
where FamilyId = @FamilyId

return

go

create procedure UpdateFamily
(
@FamilyId int,
@HeadOfFamilyClientId int,
@FamilyComment varchar(255),
@HouseholdId int,
@FamilyStateId int,
@RedFlag bit,
@Phone1 char,
@Phone2 char
)
as
--Trim Strings
SET @FamilyComment = ltrim(rtrim(@FamilyComment))

--Set Nulls
if @FamilyComment = ''
	 set @FamilyComment = NULL

update Families
set	HeadOfFamilyClientId = @HeadOfFamilyClientId,
	FamilyComment = @FamilyComment,
	HouseholdId = @HouseholdId,
	FamilyStateId = @FamilyStateId,
	RedFlag = @RedFlag,
	Phone1 = @Phone1,
	Phone2 = @Phone2
where FamilyId = @FamilyId

return

go


create procedure AddFamilyState
(
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Please Remove the last comma above

--Set Nulls
if @Description = ''
	 set @Description = NULL

insert into FamilyStates
(Description)
values
(@Description)

select @@identity
return

go

create procedure DeleteFamilyStateById
(@FamilyStateId int)
as

delete from FamilyStates
where FamilyStateId = @FamilyStateId

return

go

create procedure GetFamilyStates
as

select	FamilyStateId,
	Description
from FamilyStates

return

go

create procedure GetFamilyStateById
(@FamilyStateId int)
as

select	FamilyStateId,
	Description
from FamilyStates
where FamilyStateId = @FamilyStateId

return

go

create procedure UpdateFamilyState
(
@FamilyStateId int,
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Set Nulls
if @Description = ''
	 set @Description = NULL

update FamilyStates
set	Description = @Description
where FamilyStateId = @FamilyStateId

return

go


create procedure AddFoodHamper
(
@Weight decimal,
@PickupDate smalldatetime,
@PickupPerson varchar(255),
@ReferralId int,
@FamilyId int
)
as
--Trim Strings
SET @PickupPerson = ltrim(rtrim(@PickupPerson))

--Please Remove the last comma above

--Set Nulls
if @PickupPerson = ''
	 set @PickupPerson = NULL

insert into FoodHampers
(Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
values
(@Weight, @PickupDate, @PickupPerson, @ReferralId, @FamilyId)

select @@identity
return

go

create procedure DeleteFoodHamperById
(@FoodHamperId int)
as

delete from FoodHampers
where FoodHamperId = @FoodHamperId

return

go

create procedure GetFoodHampers
as

select	FoodHamperId,
	Weight,
	PickupDate,
	PickupPerson,
	ReferralId,
	FamilyId
from FoodHampers

return

go

create procedure GetFoodHamperById
(@FoodHamperId int)
as

select	FoodHamperId,
	Weight,
	PickupDate,
	PickupPerson,
	ReferralId,
	FamilyId
from FoodHampers
where FoodHamperId = @FoodHamperId

return

go

create procedure UpdateFoodHamper
(
@FoodHamperId int,
@Weight decimal,
@PickupDate smalldatetime,
@PickupPerson varchar(255),
@ReferralId int,
@FamilyId int
)
as
--Trim Strings
SET @PickupPerson = ltrim(rtrim(@PickupPerson))

--Set Nulls
if @PickupPerson = ''
	 set @PickupPerson = NULL

update FoodHampers
set	Weight = @Weight,
	PickupDate = @PickupDate,
	PickupPerson = @PickupPerson,
	ReferralId = @ReferralId,
	FamilyId = @FamilyId
where FoodHamperId = @FoodHamperId

return

go


create procedure AddFoodHamperState
(
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Please Remove the last comma above

--Set Nulls
if @Description = ''
	 set @Description = NULL

insert into FoodHamperStates
(Description)
values
(@Description)

select @@identity
return

go

create procedure DeleteFoodHamperStateById
(@StateId int)
as

delete from FoodHamperStates
where StateId = @StateId

return

go

create procedure GetFoodHamperStates
as

select	StateId,
	Description
from FoodHamperStates

return

go

create procedure GetFoodHamperStateById
(@StateId int)
as

select	StateId,
	Description
from FoodHamperStates
where StateId = @StateId

return

go

create procedure UpdateFoodHamperState
(
@StateId int,
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Set Nulls
if @Description = ''
	 set @Description = NULL

update FoodHamperStates
set	Description = @Description
where StateId = @StateId

return

go


create procedure AddHousehold
(
@PostalCode varchar(255),
@Address varchar(255),
@CommunityId int
)
as
--Trim Strings
SET @PostalCode = ltrim(rtrim(@PostalCode))
SET @Address = ltrim(rtrim(@Address))

--Please Remove the last comma above

--Set Nulls
if @PostalCode = ''
	 set @PostalCode = NULL
if @Address = ''
	 set @Address = NULL

insert into Households
(PostalCode, Address, CommunityId)
values
(@PostalCode, @Address, @CommunityId)

select @@identity
return

go

create procedure DeleteHouseholdById
(@HouseholdId int)
as

delete from Households
where HouseholdId = @HouseholdId

return

go

create procedure GetHouseholds
as

select	HouseholdId,
	PostalCode,
	Address,
	CommunityId
from Households

return

go

create procedure GetHouseholdById
(@HouseholdId int)
as

select	HouseholdId,
	PostalCode,
	Address,
	CommunityId
from Households
where HouseholdId = @HouseholdId

return

go

create procedure UpdateHousehold
(
@HouseholdId int,
@PostalCode varchar(255),
@Address varchar(255),
@CommunityId int
)
as
--Trim Strings
SET @PostalCode = ltrim(rtrim(@PostalCode))
SET @Address = ltrim(rtrim(@Address))

--Set Nulls
if @PostalCode = ''
	 set @PostalCode = NULL
if @Address = ''
	 set @Address = NULL

update Households
set	PostalCode = @PostalCode,
	Address = @Address,
	CommunityId = @CommunityId
where HouseholdId = @HouseholdId

return

go


create procedure AddLocation
(
@CityTown varchar(255),
@Province varchar(255),
@Country varchar(255),
@JurisdictionDescription varchar(255)
)
as
--Trim Strings
SET @CityTown = ltrim(rtrim(@CityTown))
SET @Province = ltrim(rtrim(@Province))
SET @Country = ltrim(rtrim(@Country))
SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))

--Please Remove the last comma above

--Set Nulls
if @CityTown = ''
	 set @CityTown = NULL
if @Province = ''
	 set @Province = NULL
if @Country = ''
	 set @Country = NULL
if @JurisdictionDescription = ''
	 set @JurisdictionDescription = NULL

insert into Locations
(CityTown, Province, Country, JurisdictionDescription)
values
(@CityTown, @Province, @Country, @JurisdictionDescription)

select @@identity
return

go

create procedure DeleteLocationById
(@LocationId int)
as

delete from Locations
where LocationId = @LocationId

return

go

create procedure GetLocations
as

select	LocationId,
	CityTown,
	Province,
	Country,
	JurisdictionDescription
from Locations

return

go

create procedure GetLocationById
(@LocationId int)
as

select	LocationId,
	CityTown,
	Province,
	Country,
	JurisdictionDescription
from Locations
where LocationId = @LocationId

return

go

create procedure UpdateLocation
(
@LocationId int,
@CityTown varchar(255),
@Province varchar(255),
@Country varchar(255),
@JurisdictionDescription varchar(255)
)
as
--Trim Strings
SET @CityTown = ltrim(rtrim(@CityTown))
SET @Province = ltrim(rtrim(@Province))
SET @Country = ltrim(rtrim(@Country))
SET @JurisdictionDescription = ltrim(rtrim(@JurisdictionDescription))

--Set Nulls
if @CityTown = ''
	 set @CityTown = NULL
if @Province = ''
	 set @Province = NULL
if @Country = ''
	 set @Country = NULL
if @JurisdictionDescription = ''
	 set @JurisdictionDescription = NULL

update Locations
set	CityTown = @CityTown,
	Province = @Province,
	Country = @Country,
	JurisdictionDescription = @JurisdictionDescription
where LocationId = @LocationId

return

go


create procedure AddOutgoingDonation
(
@OutgoingDonationDate smalldatetime,
@WeightDonated decimal,
@DoneeId int,
@MoneyDonated decimal
)
as
--Trim Strings

--Please Remove the last comma above

--Set Nulls

insert into OutgoingDonations
(OutgoingDonationDate, WeightDonated, DoneeId, MoneyDonated)
values
(@OutgoingDonationDate, @WeightDonated, @DoneeId, @MoneyDonated)

select @@identity
return

go

create procedure DeleteOutgoingDonationById
(@OutgoingDonationId int)
as

delete from OutgoingDonations
where OutgoingDonationId = @OutgoingDonationId

return

go

create procedure GetOutgoingDonations
as

select	OutgoingDonationId,
	OutgoingDonationDate,
	WeightDonated,
	DoneeId,
	MoneyDonated
from OutgoingDonations

return

go

create procedure GetOutgoingDonationById
(@OutgoingDonationId int)
as

select	OutgoingDonationId,
	OutgoingDonationDate,
	WeightDonated,
	DoneeId,
	MoneyDonated
from OutgoingDonations
where OutgoingDonationId = @OutgoingDonationId

return

go

create procedure UpdateOutgoingDonation
(
@OutgoingDonationId int,
@OutgoingDonationDate smalldatetime,
@WeightDonated decimal,
@DoneeId int,
@MoneyDonated decimal
)
as
--Trim Strings

--Set Nulls

update OutgoingDonations
set	OutgoingDonationDate = @OutgoingDonationDate,
	WeightDonated = @WeightDonated,
	DoneeId = @DoneeId,
	MoneyDonated = @MoneyDonated
where OutgoingDonationId = @OutgoingDonationId

return

go


create procedure AddReferralAgency
(
@LocationId int,
@Name varchar(255),
@Phone1 char,
@Phone2 char,
@Address varchar(255),
@Contact varchar(255),
@EmailAddress varchar(255),
@ReferralAgencyCategoryId int
)
as
--Trim Strings
SET @Name = ltrim(rtrim(@Name))
SET @Address = ltrim(rtrim(@Address))
SET @Contact = ltrim(rtrim(@Contact))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))

--Please Remove the last comma above

--Set Nulls
if @Name = ''
	 set @Name = NULL
if @Address = ''
	 set @Address = NULL
if @Contact = ''
	 set @Contact = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL

insert into ReferralAgencies
(LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress, ReferralAgencyCategoryId)
values
(@LocationId, @Name, @Phone1, @Phone2, @Address, @Contact, @EmailAddress, @ReferralAgencyCategoryId)

select @@identity
return

go

create procedure DeleteReferralAgencyById
(@ReferralAgencyId int)
as

delete from ReferralAgencies
where ReferralAgencyId = @ReferralAgencyId

return

go

create procedure GetReferralAgencies
as

select	ReferralAgencyId,
	LocationId,
	Name,
	Phone1,
	Phone2,
	Address,
	Contact,
	EmailAddress,
	ReferralAgencyCategoryId
from ReferralAgencies

return

go

create procedure GetReferralAgencyById
(@ReferralAgencyId int)
as

select	ReferralAgencyId,
	LocationId,
	Name,
	Phone1,
	Phone2,
	Address,
	Contact,
	EmailAddress,
	ReferralAgencyCategoryId
from ReferralAgencies
where ReferralAgencyId = @ReferralAgencyId

return

go

create procedure UpdateReferralAgency
(
@ReferralAgencyId int,
@LocationId int,
@Name varchar(255),
@Phone1 char,
@Phone2 char,
@Address varchar(255),
@Contact varchar(255),
@EmailAddress varchar(255),
@ReferralAgencyCategoryId int
)
as
--Trim Strings
SET @Name = ltrim(rtrim(@Name))
SET @Address = ltrim(rtrim(@Address))
SET @Contact = ltrim(rtrim(@Contact))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))

--Set Nulls
if @Name = ''
	 set @Name = NULL
if @Address = ''
	 set @Address = NULL
if @Contact = ''
	 set @Contact = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL

update ReferralAgencies
set	LocationId = @LocationId,
	Name = @Name,
	Phone1 = @Phone1,
	Phone2 = @Phone2,
	Address = @Address,
	Contact = @Contact,
	EmailAddress = @EmailAddress,
	ReferralAgencyCategoryId = @ReferralAgencyCategoryId
where ReferralAgencyId = @ReferralAgencyId

return

go


create procedure AddReferralAgencyCategory
(
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Please Remove the last comma above

--Set Nulls
if @Description = ''
	 set @Description = NULL

insert into ReferralAgencyCategories
(Description)
values
(@Description)

select @@identity
return

go

create procedure DeleteReferralAgencyCategoryById
(@ReferralAgencyCategoryId int)
as

delete from ReferralAgencyCategories
where ReferralAgencyCategoryId = @ReferralAgencyCategoryId

return

go

create procedure GetReferralAgencyCategories
as

select	ReferralAgencyCategoryId,
	Description
from ReferralAgencyCategories

return

go

create procedure GetReferralAgencyCategoryById
(@ReferralAgencyCategoryId int)
as

select	ReferralAgencyCategoryId,
	Description
from ReferralAgencyCategories
where ReferralAgencyCategoryId = @ReferralAgencyCategoryId

return

go

create procedure UpdateReferralAgencyCategory
(
@ReferralAgencyCategoryId int,
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Set Nulls
if @Description = ''
	 set @Description = NULL

update ReferralAgencyCategories
set	Description = @Description
where ReferralAgencyCategoryId = @ReferralAgencyCategoryId

return

go


create procedure AddReferral
(
@ReferralAgencyId int,
@ReferralDetails varchar(255),
@NumberOfAdults smallint,
@NumberOfChildren smallint,
@ReferralDate smalldatetime,
@ClientName varchar(255)
)
as
--Trim Strings
SET @ReferralDetails = ltrim(rtrim(@ReferralDetails))
SET @ClientName = ltrim(rtrim(@ClientName))

--Please Remove the last comma above

--Set Nulls
if @ReferralDetails = ''
	 set @ReferralDetails = NULL
if @ClientName = ''
	 set @ClientName = NULL

insert into Referrals
(ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
values
(@ReferralAgencyId, @ReferralDetails, @NumberOfAdults, @NumberOfChildren, @ReferralDate, @ClientName)

select @@identity
return

go

create procedure DeleteReferralById
(@ReferralId int)
as

delete from Referrals
where ReferralId = @ReferralId

return

go

create procedure GetReferrals
as

select	ReferralId,
	ReferralAgencyId,
	ReferralDetails,
	NumberOfAdults,
	NumberOfChildren,
	ReferralDate,
	ClientName
from Referrals

return

go

create procedure GetReferralById
(@ReferralId int)
as

select	ReferralId,
	ReferralAgencyId,
	ReferralDetails,
	NumberOfAdults,
	NumberOfChildren,
	ReferralDate,
	ClientName
from Referrals
where ReferralId = @ReferralId

return

go

create procedure UpdateReferral
(
@ReferralId int,
@ReferralAgencyId int,
@ReferralDetails varchar(255),
@NumberOfAdults smallint,
@NumberOfChildren smallint,
@ReferralDate smalldatetime,
@ClientName varchar(255)
)
as
--Trim Strings
SET @ReferralDetails = ltrim(rtrim(@ReferralDetails))
SET @ClientName = ltrim(rtrim(@ClientName))

--Set Nulls
if @ReferralDetails = ''
	 set @ReferralDetails = NULL
if @ClientName = ''
	 set @ClientName = NULL

update Referrals
set	ReferralAgencyId = @ReferralAgencyId,
	ReferralDetails = @ReferralDetails,
	NumberOfAdults = @NumberOfAdults,
	NumberOfChildren = @NumberOfChildren,
	ReferralDate = @ReferralDate,
	ClientName = @ClientName
where ReferralId = @ReferralId

return

go


create procedure AddTotalAsset
(
@TotalFood decimal,
@TotalMoney decimal
)
as
--Trim Strings

--Please Remove the last comma above

--Set Nulls

insert into TotalAssets
(TotalFood, TotalMoney)
values
(@TotalFood, @TotalMoney)

select @@identity
return

go

create procedure DeleteTotalAssetById
(@TotalAssetId int)
as

delete from TotalAssets
where TotalAssetId = @TotalAssetId

return

go

create procedure GetTotalAssets
as

select	TotalAssetId,
	TotalFood,
	TotalMoney
from TotalAssets

return

go

create procedure GetTotalAssetById
(@TotalAssetId int)
as

select	TotalAssetId,
	TotalFood,
	TotalMoney
from TotalAssets
where TotalAssetId = @TotalAssetId

return

go

create procedure UpdateTotalAsset
(
@TotalAssetId int,
@TotalFood decimal,
@TotalMoney decimal
)
as
--Trim Strings

--Set Nulls

update TotalAssets
set	TotalFood = @TotalFood,
	TotalMoney = @TotalMoney
where TotalAssetId = @TotalAssetId

return

go


create procedure AddVolunteerHour
(
@VolunteerId int,
@Date smalldatetime,
@HoursWorked int
)
as
--Trim Strings

--Please Remove the last comma above

--Set Nulls

insert into VolunteerHours
(VolunteerId, Date, HoursWorked)
values
(@VolunteerId, @Date, @HoursWorked)

select @@identity
return

go

create procedure DeleteVolunteerHourById
(@VolunteerHourId int)
as

delete from VolunteerHours
where VolunteerHourId = @VolunteerHourId

return

go

create procedure GetVolunteerHours
as

select	VolunteerHourId,
	VolunteerId,
	Date,
	HoursWorked
from VolunteerHours

return

go

create procedure GetVolunteerHourById
(@VolunteerHourId int)
as

select	VolunteerHourId,
	VolunteerId,
	Date,
	HoursWorked
from VolunteerHours
where VolunteerHourId = @VolunteerHourId

return

go

create procedure UpdateVolunteerHour
(
@VolunteerHourId int,
@VolunteerId int,
@Date smalldatetime,
@HoursWorked int
)
as
--Trim Strings

--Set Nulls

update VolunteerHours
set	VolunteerId = @VolunteerId,
	Date = @Date,
	HoursWorked = @HoursWorked
where VolunteerHourId = @VolunteerHourId

return

go


create procedure AddVolunteer
(
@LocationId int,
@VolunteerTypeId int,
@Gender char,
@FirstName varchar(255),
@LastName varchar(255),
@MiddleName varchar(255),
@Address varchar(255),
@Phone1 char,
@Phone2 char,
@Birthdate smalldatetime,
@StartDate smalldatetime,
@EmailAddress varchar(255),
@PostalCode char,
@EmergencyContactName varchar(255),
@EmergencyContactPhone char
)
as
--Trim Strings
SET @FirstName = ltrim(rtrim(@FirstName))
SET @LastName = ltrim(rtrim(@LastName))
SET @MiddleName = ltrim(rtrim(@MiddleName))
SET @Address = ltrim(rtrim(@Address))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))
SET @EmergencyContactName = ltrim(rtrim(@EmergencyContactName))

--Please Remove the last comma above

--Set Nulls
if @FirstName = ''
	 set @FirstName = NULL
if @LastName = ''
	 set @LastName = NULL
if @MiddleName = ''
	 set @MiddleName = NULL
if @Address = ''
	 set @Address = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL
if @EmergencyContactName = ''
	 set @EmergencyContactName = NULL

insert into Volunteers
(LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)
values
(@LocationId, @VolunteerTypeId, @Gender, @FirstName, @LastName, @MiddleName, @Address, @Phone1, @Phone2, @Birthdate, @StartDate, @EmailAddress, @PostalCode, @EmergencyContactName, @EmergencyContactPhone)

select @@identity
return

go

create procedure DeleteVolunteerById
(@VolunteerId int)
as

delete from Volunteers
where VolunteerId = @VolunteerId

return

go

create procedure GetVolunteers
as

select	VolunteerId,
	LocationId,
	VolunteerTypeId,
	Gender,
	FirstName,
	LastName,
	MiddleName,
	Address,
	Phone1,
	Phone2,
	Birthdate,
	StartDate,
	EmailAddress,
	PostalCode,
	EmergencyContactName,
	EmergencyContactPhone
from Volunteers

return

go

create procedure GetVolunteerById
(@VolunteerId int)
as

select	VolunteerId,
	LocationId,
	VolunteerTypeId,
	Gender,
	FirstName,
	LastName,
	MiddleName,
	Address,
	Phone1,
	Phone2,
	Birthdate,
	StartDate,
	EmailAddress,
	PostalCode,
	EmergencyContactName,
	EmergencyContactPhone
from Volunteers
where VolunteerId = @VolunteerId

return

go

create procedure UpdateVolunteer
(
@VolunteerId int,
@LocationId int,
@VolunteerTypeId int,
@Gender char,
@FirstName varchar(255),
@LastName varchar(255),
@MiddleName varchar(255),
@Address varchar(255),
@Phone1 char,
@Phone2 char,
@Birthdate smalldatetime,
@StartDate smalldatetime,
@EmailAddress varchar(255),
@PostalCode char,
@EmergencyContactName varchar(255),
@EmergencyContactPhone char
)
as
--Trim Strings
SET @FirstName = ltrim(rtrim(@FirstName))
SET @LastName = ltrim(rtrim(@LastName))
SET @MiddleName = ltrim(rtrim(@MiddleName))
SET @Address = ltrim(rtrim(@Address))
SET @EmailAddress = ltrim(rtrim(@EmailAddress))
SET @EmergencyContactName = ltrim(rtrim(@EmergencyContactName))

--Set Nulls
if @FirstName = ''
	 set @FirstName = NULL
if @LastName = ''
	 set @LastName = NULL
if @MiddleName = ''
	 set @MiddleName = NULL
if @Address = ''
	 set @Address = NULL
if @EmailAddress = ''
	 set @EmailAddress = NULL
if @EmergencyContactName = ''
	 set @EmergencyContactName = NULL

update Volunteers
set	LocationId = @LocationId,
	VolunteerTypeId = @VolunteerTypeId,
	Gender = @Gender,
	FirstName = @FirstName,
	LastName = @LastName,
	MiddleName = @MiddleName,
	Address = @Address,
	Phone1 = @Phone1,
	Phone2 = @Phone2,
	Birthdate = @Birthdate,
	StartDate = @StartDate,
	EmailAddress = @EmailAddress,
	PostalCode = @PostalCode,
	EmergencyContactName = @EmergencyContactName,
	EmergencyContactPhone = @EmergencyContactPhone
where VolunteerId = @VolunteerId

return

go


create procedure AddVolunteerType
(
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Please Remove the last comma above

--Set Nulls
if @Description = ''
	 set @Description = NULL

insert into VolunteerTypes
(Description)
values
(@Description)

select @@identity
return

go

create procedure DeleteVolunteerTypeById
(@VolunteerTypeId int)
as

delete from VolunteerTypes
where VolunteerTypeId = @VolunteerTypeId

return

go

create procedure GetVolunteerTypes
as

select	VolunteerTypeId,
	Description
from VolunteerTypes

return

go

create procedure GetVolunteerTypeById
(@VolunteerTypeId int)
as

select	VolunteerTypeId,
	Description
from VolunteerTypes
where VolunteerTypeId = @VolunteerTypeId

return

go

create procedure UpdateVolunteerType
(
@VolunteerTypeId int,
@Description varchar(255)
)
as
--Trim Strings
SET @Description = ltrim(rtrim(@Description))

--Set Nulls
if @Description = ''
	 set @Description = NULL

update VolunteerTypes
set	Description = @Description
where VolunteerTypeId = @VolunteerTypeId

return

go

use master

go

use StAlbertFoodBank

-- reseed all tables to not mess up inserts
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,1) END'

go

INSERT INTO TotalAssets (TotalAssetId, TotalFood, TotalMoney)
	VALUES (1, 6940.16, 850.74)

go

INSERT INTO Locations (CityTown, Province, Country)
	VALUES ('St. Albert', 'Alberta', 'Canada')
INSERT INTO Locations (CityTown, Province, Country)
	VALUES ('Morinville', 'Alberta', 'Canada')
INSERT INTO Locations (CityTown, Province, Country)
	VALUES ('Alexander', 'Alberta', 'Canada')
INSERT INTO Locations (CityTown, Province, Country)
	VALUES ('Edmonton', 'Alberta', 'Canada')

go

INSERT INTO VolunteerTypes (Description)
	VALUES ('Skilled')
INSERT INTO VolunteerTypes (Description)
	VALUES ('Unskilled')

go

INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)
	VALUES (1, 2, 'M', 'Luke', 'Skywalker', '', '6602 Millenium Way', '7809654435', '7804641153', CONVERT(DATETIME, '1961-08-24',120), CONVERT(DATETIME, '2006-12-05',120), 'iamjedi@force.net', 'P2U9Z7', 'Han Solo', '7802486653')
INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)
	VALUES (4, 1, 'F', 'Marge', 'Simpson', '', '742 Evergreen Terrace', '7805512256', '', CONVERT(DATETIME, '1956-02-06',120), CONVERT(DATETIME, '1998-05-25',120), 'marjorie@springfield.com', 'N3O9S8', 'Homer Simpson', '7805512256')
INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)
	VALUES (1, 1, 'M', 'Jean', 'Picard', 'Luc', '314 Galaxy Dr.', '7806431687', '', CONVERT(DATETIME, '1948-07-13',120), CONVERT(DATETIME, '2002-08-14',120), 'captain@enterprise.ufo', 'R3I7B1', 'Diana Troi', '7806685530')
INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)
	VALUES (2, 1, 'F', 'Judge', 'Judy', '', '1199 Justice Court', '780728391', '', CONVERT(DATETIME, '1965-06-11',120), CONVERT(DATETIME, '2008-01-09',120), 'judy@gavel.com', 'P9M4N7', 'Officer Dan', '7804486209')
INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)
	VALUES (1, 2, 'F', 'Olive', 'Oyl', '', '62 Oceanview Gardens', '7805575216', '', CONVERT(DATETIME, '1919-12-19',120), CONVERT(DATETIME, '2005-05-16',120), 'damsel@distress.net', 'Y3M8S2', 'Popeye', '7807469234')

go

INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '1998-05-25',120), 6)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '2000-02-27',120), 8)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (3, CONVERT(DATETIME, '2002-08-14',120), 8)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (5, CONVERT(DATETIME, '2005-05-16',120), 4)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (1, CONVERT(DATETIME, '2006-12-05',120), 6)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '2007-11-30',120), 5)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (4, CONVERT(DATETIME, '2008-01-09',120), 8)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '2008-01-09',120), 8)

go

INSERT INTO Communities (LocationId, Name)
	VALUES (1, 'Akinsdale')
INSERT INTO Communities (LocationId, Name)
	VALUES (1, 'Braeside')
INSERT INTO Communities (LocationId, Name)
	VALUES (1, 'Deer Ridge')
INSERT INTO Communities (LocationId, Name)
	VALUES (1, 'Grandin')
INSERT INTO Communities (LocationId, Name)
	VALUES (4, 'Edmonton')
INSERT INTO Communities (LocationId, Name)
	VALUES (2, 'Morinville')
INSERT INTO Communities (LocationId, Name)
	VALUES (3, 'Alexander')

go

INSERT INTO Households (PostalCode, Address, CommunityId)
	VALUES ('F9M4B7', '28 Rodeo Dr.', 1)
INSERT INTO Households (PostalCode, Address, CommunityId)
	VALUES ('S5D9Y3', '6543 Pine St.', 2)
INSERT INTO Households (PostalCode, Address, CommunityId)
	VALUES ('I3Q8D6', '16 Lakewood Dr.', 6)
INSERT INTO Households (PostalCode, Address, CommunityId)
	VALUES ('H6D5S9', '591 Eastwood', 7)
INSERT INTO Households (PostalCode, Address, CommunityId)
	VALUES ('U1D6E4', '93 Akinsdale Estates', 1)

go

INSERT INTO FamilyStates (Description)
	VALUES ('Single Parent')
INSERT INTO FamilyStates (Description)
	VALUES ('Two Parents')
INSERT INTO FamilyStates (Description)
	VALUES ('Single Person')
INSERT INTO FamilyStates (Description)
	VALUES ('Couple without Children')

go

INSERT INTO Families (HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2)
	VALUES (null, '', 2, 3, 0, '7803468164', '')
INSERT INTO Families (HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2)
	VALUES (null, 'Sometimes tries to double-dip.', 5, 2, 0, '7804684611', '7809953315')
INSERT INTO Families (HeadOfFamilyClientId, FamilyComment, HouseholdId, FamilyStateId, RedFlag, Phone1, Phone2)
	VALUES (null, '', 4, 1, 0, '7809643687', '')

go

INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId)
	VALUES ('Peanut allergy', 'M', 'Lester','Arthur','A', CONVERT(DATETIME, '1968-06-18',120), '', '64256-6684', 1)
INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId)
	VALUES ('', 'M', 'John','Fogerty','', CONVERT(DATETIME, '1956-11-04',120), '', '24653-4460', 2)
INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate,  EmailAddress, AhcNumber, FamilyId)
	VALUES ('', 'F', 'Julia','Roberts','', CONVERT(DATETIME, '1964-08-07',120), '', '64256-6684', 2)
INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate,  EmailAddress, AhcNumber, FamilyId)
	VALUES ('Diabetic', 'F', 'Susie','Fogerty','Q', CONVERT(DATETIME, '1992-03-24',120), '', '92207-8034', 2)
INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId)
	VALUES ('', 'F', 'Edna','Krabapple','', CONVERT(DATETIME, '1974-06-18',120), '', '64256-6519', 3)
INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId)
	VALUES ('', 'M', 'Teddy','Krabapple','', CONVERT(DATETIME, '2004-10-20',120),'', '83614-6481', 3)
INSERT INTO Clients (HealthInfo, Gender, FirstName, LastName, MiddleName, Birthdate, EmailAddress, AhcNumber, FamilyId)
	VALUES ('', 'M', 'Billy','Krabapple','', CONVERT(DATETIME, '2006-07-02',120), '', '64256-8608', 3)

go

Update Families
	Set HeadOfFamilyClientId  = (select clientid from clients where firstname = 'Lester')
	Where phone1 = '7803468164'
Update Families
	Set HeadOfFamilyClientId  = (select clientid from clients where firstname = 'Julia')
	Where phone1 = '7804684611'
Update Families
	Set HeadOfFamilyClientId  = (select clientid from clients where firstname = 'Edna')
	Where phone1 = '7809643687'

go

INSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut)
	VALUES (1, 2, '')
INSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut)
	VALUES (3, 1, CONVERT(DATETIME, '2005-05-22',120))
INSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut)
	VALUES (3, 5, '')
INSERT INTO ClientHouseholdHistory (ClientId, HouseholdId, DateMovedOut)
	VALUES (5, 4, '')

go

INSERT INTO ReferralAgencyCategories (Description)
    VALUES ('Social Agencies')
INSERT INTO ReferralAgencyCategories (Description)
    VALUES ('Churches')
INSERT INTO ReferralAgencyCategories (Description)
    VALUES ('Doctors')
INSERT INTO ReferralAgencyCategories (Description)
    VALUES ('Others')

go

INSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress)
	VALUES (1, 'St. Albert Health Unit', '7809643152', '', '642 Pleasantview St.', 'Janice', 'janicet@sahu.org')
INSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress)
	VALUES (1, 'FCSS (St. Albert)', '7806842615', '', '23 Charcoal Rd.', 'Humphrey', 'hbogart@fcss.net')
INSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress)
	VALUES (3, 'Alexander Reserve', '7805386617', '7809643153', '83 Crestwood Way', 'Lisa', 'alexander@reserve.net')
INSERT INTO ReferralAgencies (LocationId, Name, Phone1, Phone2, Address, Contact, EmailAddress)
	VALUES (1, 'Other', '', '', '', '', '')

go

INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (1, 'Lester is allergic to peanuts', 1, 0, CONVERT(DATETIME, '2007-08-14',120), 'Lester Arthur')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (1, '', 2, 1, CONVERT(DATETIME, '2007-11-18',120), 'Julia Roberts')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (2, '', 1, 0, CONVERT(DATETIME, '2007-10-27',120), 'Lester Arthur')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (3, '', 1, 2, CONVERT(DATETIME, '2008-01-05',120), 'Edna Krabapple')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (4, '', 1, 2, CONVERT(DATETIME, '2008-02-24',120), 'Edna Krabapple')

go

INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (35, CONVERT(DATETIME, '2007-08-18',120), 'Lester Arthur', 1, 1)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (40, CONVERT(DATETIME, '2007-11-24',120), 'John Fogerty', 2, 2)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (35, CONVERT(DATETIME, '2007-08-18',120), 'Lester Arthur', 3, 1)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (50, CONVERT(DATETIME, '2008-01-09',120), 'Edna Krabapple', 4, 3)

go

INSERT INTO DonorTypes (Description)
	VALUES ('Corporate')
INSERT INTO DonorTypes (Description)
	VALUES ('Individual')
INSERT INTO DonorTypes (Description)
	VALUES ('Anonymous')

go

INSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact)
	VALUES (1, 2, 'Fred Savage', '7806243150', '', '681 Forester Ave.', 'fsavage@wonder.net', '')
INSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact)
	VALUES (1, 3, 'Anonymous', '', '', '', '', '')
INSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact)
	VALUES (2, 1, 'Safeway Ltd.', '7809564481', '', '21 Possum St.', '', 'Felicity')
INSERT INTO Donors (LocationId, TypeId, Name, Phone1, Phone2, Address, EmailAddress, Contact)
	VALUES (3, 2, 'Sharon Stone', '7807224688', '7804681213', '55 Neptune Way', 'sharon.stone@movies.com', '')

go

INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2007-05-09',120), null, 100)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (2, CONVERT(DATETIME, '2007-06-15',120), null, 550)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2007-10-01',120), 1000, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2008-01-01',120), 1500, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (4, CONVERT(DATETIME, '2008-01-24',120), null, 200)

go

INSERT INTO Donees (DoneeName)
    VALUES ('Salvation Army')
INSERT INTO Donees (DoneeName)
    VALUES ('Unity Surplus')
INSERT INTO Donees (DoneeName)
    VALUES ('Garbage')
INSERT INTO Donees (DoneeName)
    VALUES ('Other')

go

INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2007-05-09',120), null, 100)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (2, CONVERT(DATETIME, '2007-06-15',120), null, 550)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2007-10-01',120), 1000, null)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2008-01-01',120), 1500, null)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (4, CONVERT(DATETIME, '2008-01-24',120), null, 200)

go

INSERT INTO ClientTypes(Description)
    VALUES ('Aboriginal')
INSERT INTO ClientTypes(Description)
    VALUES ('New Immigrant / Refugee')
INSERT INTO ClientTypes(Description)
    VALUES ('Post Secondary')

go

INSERT INTO ClientTypeInformation (ClientId, ClientTypeId)
    VALUES (1, 1)
INSERT INTO ClientTypeInformation (ClientId, ClientTypeId)
    VALUES (4, 1)
INSERT INTO ClientTypeInformation (ClientId, ClientTypeId)
    VALUES (1, 3)
INSERT INTO ClientTypeInformation (ClientId, ClientTypeId)
    VALUES (2, 3)
INSERT INTO ClientTypeInformation (ClientId, ClientTypeId)
    VALUES (2, 1)
INSERT INTO ClientTypeInformation (ClientId, ClientTypeId)
    VALUES (3, 3)
INSERT INTO ClientTypeInformation (ClientId, ClientTypeId)
    VALUES (1, 2)

--Added 2008.03.12 11:38 - Mathieu
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2008-03-02',120), 50, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (2, CONVERT(DATETIME, '2008-03-03',120), 10, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2008-03-08',120), 945, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2008-03-08',120), 1500, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (4, CONVERT(DATETIME, '2008-03-10',120), 670, null)

INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2007-01-02',120), 25, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (2, CONVERT(DATETIME, '2007-01-18',120), 60, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2007-02-08',120), 226, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2007-03-05',120), 1290, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (4, CONVERT(DATETIME, '2007-03-10',120), 670, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2007-03-28',120), 62, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (2, CONVERT(DATETIME, '2008-01-21',120), 39, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2008-01-30',120), 278, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2008-02-17',120), 920, null)
INSERT INTO Donations (DonorId, DonationDate, WeightDonated, MoneyDonated)
	VALUES (4, CONVERT(DATETIME, '2008-02-25',120), 75, null)


INSERT INTO Volunteers (LocationId, VolunteerTypeId, Gender, FirstName, LastName, MiddleName, Address, Phone1, Phone2, Birthdate, StartDate, EmailAddress, PostalCode, EmergencyContactName, EmergencyContactPhone)
	VALUES (1, 2, 'F', 'Jaina', 'Proudmoore', '', '45 Elwyn Forest', '7802468495', '', CONVERT(DATETIME, '1961-03-17',120), CONVERT(DATETIME, '2006-03-15',120), 'jaina@nature.net', 'U6S9F1', 'Lilly Proudmoore', '7807468543')

INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (1, '', 2, 3, CONVERT(DATETIME, '2008-03-05',120), 'Sweeney Todd')

-- #1 return columns: Volunteer, Birthday, Upcoming Age
go
drop procedure GetVolunteerBirthdays
go
CREATE PROCEDURE GetVolunteerBirthdays
AS
SELECT FirstName + ' ' + LastName as "Volunteer",
	   datename(month,Birthdate) + ' ' + convert(varchar, datepart(dd,Birthdate)) as "Birthday",
       datediff(yy, Birthdate, getdate()) as "UpcomingAge"
FROM Volunteers
WHERE datediff(dd, getdate(), dateadd(yy, datediff(yy, Birthdate, getdate()),Birthdate)) < 7
AND datediff(dd, getdate(), dateadd(yy, datediff(yy, Birthdate, getdate()),Birthdate)) >=0
RETURN @@identity
GO

-- #2 return columns: Agency, Date Referred, Client
drop procedure GetOutstandingReferrals
go
CREATE PROCEDURE GetOutstandingReferrals
AS
SELECT ra.Name "Agency",
	   datename(month,ReferralDate) + ' ' + convert(varchar, datepart(dd,ReferralDate)) +', '+ convert(varchar, datepart(yy,ReferralDate)) "DateReferred",
	   ClientName "Client"
FROM Referrals r, ReferralAgencies ra
WHERE ReferralId NOT IN(SELECT ReferralId FROM FoodHampers)
AND r.ReferralAgencyId = ra.ReferralAgencyIdRETURN @@identity
GO

-- #3 return columns: 
drop procedure GetTotalServed
go
CREATE PROCEDURE GetTotalServed
AS
DECLARE @children int, @adults int, @total int
SET @children =(SELECT sum(NumberOfChildren)
				FROM Referrals
				WHERE ReferralId in (SELECT ReferralId FROM FoodHampers))
SET @adults =(	SELECT sum(NumberOfAdults)
				FROM Referrals
				WHERE ReferralId in (SELECT ReferralId FROM FoodHampers))
SET @total =(	SELECT sum(NumberOfAdults+NumberOfChildren)
				FROM Referrals
				WHERE ReferralId in (SELECT ReferralId FROM FoodHampers))
SELECT @children "Children", @adults "Adults", @total "Total"
RETURN @@identity
GO

-- #4 return columns: TotalAssetId, TotalFood, TotalMoney
-- exec GetTotalAssets

-- #5 return columns: 
drop procedure GetTotalSurplus
go
CREATE PROCEDURE GetTotalSurplus
AS
SELECT sum(WeightDonated) "TotalFoodSurplus"
FROM OutgoingDonations
RETURN @@identity
GO



-- Added 2008.03.12 15:00 - Mathieu
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2007-03-09',120), 240, 0)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (2, CONVERT(DATETIME, '2007-03-15',120), 100, 0)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2007-01-08',120), 850, 0)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (3, CONVERT(DATETIME, '2007-01-16',120), 400, 0)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (4, CONVERT(DATETIME, '2008-02-24',120), 300, 0)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2008-02-04',120), 400, 0)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (2, CONVERT(DATETIME, '2008-03-01',120), 150, 0)
INSERT INTO OutgoingDonations (DoneeId, OutgoingDonationDate, WeightDonated, MoneyDonated)
	VALUES (1, CONVERT(DATETIME, '2008-03-10',120), 180, 0)

INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (1, '', 1, 0, CONVERT(DATETIME, '2007-01-14',120), 'Lester Arthur')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (1, '', 1, 2, CONVERT(DATETIME, '2007-02-05',120), 'Edna Krabapple')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (1, '', 2, 1, CONVERT(DATETIME, '2007-02-18',120), 'Julia Roberts')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (3, '', 1, 2, CONVERT(DATETIME, '2008-03-05',120), 'Edna Krabapple')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (4, '', 2, 1, CONVERT(DATETIME, '2008-03-08',120), 'Julia Roberts')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (2, '', 1, 0, CONVERT(DATETIME, '2007-03-14',120), 'Lester Arthur')
INSERT INTO Referrals (ReferralAgencyId, ReferralDetails, NumberOfAdults, NumberOfChildren, ReferralDate, ClientName)
	VALUES (1, '', 1, 2, CONVERT(DATETIME, '2007-03-27',120), 'Edna Krabapple')

INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (50, CONVERT(DATETIME, '2007-03-27',120), 'Edna Krabapple', 6, 3)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (30, CONVERT(DATETIME, '2007-01-16',120), 'Lester Arthur', 7, 1)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (50, CONVERT(DATETIME, '2007-02-07',120), 'Edna Krabapple', 8, 3)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (40, CONVERT(DATETIME, '2007-02-18',120), 'Julia Roberts', 9, 2)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (55, CONVERT(DATETIME, '2008-03-05',120), 'Edna Krabapple', 10, 3)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (25, CONVERT(DATETIME, '2008-03-08',120), 'Julia Roberts', 11, 2)
INSERT INTO FoodHampers (Weight, PickupDate, PickupPerson, ReferralId, FamilyId)
	VALUES (35, CONVERT(DATETIME, '2007-03-14',120), 'Lester Arthur', 12, 1)

INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '2007-03-04',120), 7)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '2007-03-09',120), 5)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (3, CONVERT(DATETIME, '2007-03-14',120), 4)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (5, CONVERT(DATETIME, '2007-03-21',120), 8)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (1, CONVERT(DATETIME, '2008-03-04',120), 5)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '2008-03-09',120), 6)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (4, CONVERT(DATETIME, '2008-03-11',120), 6)
INSERT INTO VolunteerHours (VolunteerId, Date, HoursWorked)
	VALUES (2, CONVERT(DATETIME, '2008-03-12',120), 8)

use master