USE [VIGIL-VRM]
GO


-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_audit_info]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_audit_info]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_casefile_investigators]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_casefile_investigators]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_interview_attendees]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_interview_attendees]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_interview_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_interview_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_interview_subjects]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_interview_subjects]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_interview_transcribers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_interview_transcribers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_recorder]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_recorder]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_section]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_section]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_interview_type]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_interview_type]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_offense]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_offense]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_location]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_location]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_personnel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_personnel]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_casefile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_casefile]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_casefile_interviews]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_casefile_interviews]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_casefile_investigators]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_casefile_investigators]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_countries]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_countries]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_country_id]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_country_id]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_export_files]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_export_files]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_export]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_export]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_exports]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_exports]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_interview]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_interview]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_interview_attendees]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_interview_attendees]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_interview_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_interview_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_interview_subjects]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_interview_subjects]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_interview_transcribers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_interview_transcribers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_interview_type]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_interview_type]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_location]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_location]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_offenses]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_offenses]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_personal_info]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_personal_info]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_personnel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_personnel]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_provinces]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_provinces]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_recorder]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_recorder]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_assigned_recorders]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_assigned_recorders]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_section]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_section]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_subject]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_subject]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_personnel_types]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_personnel_types]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_user]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_user]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_admin_count]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_admin_count]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_casefile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_casefile]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_casefile_investigator]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_casefile_investigator]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_export]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_export]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_export_file]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_export_file]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_interview]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_interview]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_interview_attendee]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_interview_attendee]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_interview_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_interview_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_interview_subject]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_interview_subject]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_interview_transcriber]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_interview_transcriber]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_location]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_location]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_offense]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_offense]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_personnel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_personnel]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_recorder]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_recorder]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_section]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_section]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_interview_type]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_interview_type]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_subject]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_subject]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_is_person_busy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_is_person_busy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_is_subject_busy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_is_subject_busy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_is_section_busy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_is_section_busy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_is_interview_type_busy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_is_interview_type_busy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_is_offense_busy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_is_offense_busy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_is_location_busy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_is_location_busy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_is_unique_fileno]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_is_unique_fileno]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_audit]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_audit]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_touch_casefile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_touch_casefile]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_casefile]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_casefile]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_interview]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_interview]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_interview_footage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_interview_footage]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_location]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_location]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_personnel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_personnel]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_recorder]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_recorder]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_section]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_section]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_interview_type]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_interview_type]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_offense]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_offense]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_subject]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_subject]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_interview]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_interview]
GO


-- CREATE

CREATE PROCEDURE [dbo].[sp_insert_audit_info]
@source as varchar(50),
@message as varchar(200),
@auditTime as datetime,
@errorLevel as int,
@loggedOnUserID as int
AS
	INSERT INTO audit (source, message, audit_time, error_level, personnel_id_fk) VALUES (@source, @message, @auditTime, @errorLevel, @loggedOnUserID)

GO

CREATE PROCEDURE [dbo].[sp_delete_casefile_investigators]
@casefileID as int
AS
	DELETE FROM casefile_investigators WHERE casefile_id_fk = @casefileID
GO

CREATE PROCEDURE [dbo].[sp_delete_interview_attendees]
@interviewID as int
AS
	DELETE FROM interview_attendees WHERE interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_delete_interview_footage]
@interviewID as int
AS
	DELETE FROM interview_footage WHERE interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_delete_interview_subjects]
@interviewID as int
AS
	DELETE FROM interview_subjects WHERE interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_delete_interview_transcribers]
@interviewID as int
AS
	DELETE FROM interview_transcribers WHERE interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_delete_recorder]
@recorderID as int
AS
DELETE FROM recorders WHERE id = @recorderID
GO

CREATE PROCEDURE [dbo].[sp_delete_section]
@sectionID as int
AS
DELETE FROM sections WHERE id = @sectionID
GO

CREATE PROCEDURE [dbo].[sp_delete_interview_type]
@interviewTypeID as int
AS
DELETE FROM interview_types WHERE id = @interviewTypeID
GO

CREATE PROCEDURE [dbo].[sp_delete_offense]
@offenseID as int
AS
DELETE FROM offenses WHERE id = @offenseID
GO

CREATE PROCEDURE [dbo].[sp_delete_location]
@locationID as int
AS
DELETE FROM locations WHERE id = @locationID
GO

CREATE PROCEDURE [dbo].[sp_delete_personnel]
@ID as int
AS
DELETE FROM personnel WHERE id = @ID
GO

CREATE PROCEDURE [dbo].[sp_get_casefile]
@casefileID as int = 0
AS
	IF @casefileID = 0
		SELECT id, file_no as fileNo, section_id_fk as sectionID, offense_id_fk as offenseID, last_updated as lastUpdated, notes, private
		FROM casefiles
	ELSE
		SELECT id, file_no as fileNo, section_id_fk as sectionID, offense_id_fk as offenseID, last_updated as lastUpdated, notes, private
		FROM casefiles
		WHERE id = @casefileID
GO

CREATE PROCEDURE [dbo].[sp_get_casefile_interviews]
@casefileID as int
AS
	SELECT id, interview_date as interviewDate, interview_type_id_fk as interviewTypeID, location_id_fk as locationID, notes, private
	FROM interviews
	WHERE casefile_id_fk = @casefileID
GO

CREATE PROCEDURE [dbo].[sp_get_casefile_investigators]
@casefileID as int
AS
	SELECT p.id, p.lastname, p.firstname, p.section_id_fk as sectionID, p.phone, p.country_id_fk as countryID, p.province_id_fk as provinceID, p.notes
	FROM personnel p, casefile_investigators f, personnel_type pt
	WHERE p.id = f.personnel_id_fk AND  p.type_id_fk = pt.id and pt.description = 'investigator'  and f.casefile_id_fk = @casefileID
GO

CREATE PROCEDURE [dbo].[sp_get_countries] AS
SELECT * FROM countries
ORDER BY name
GO

CREATE PROCEDURE [dbo].[sp_get_country_id]
@country as varchar(50)
 AS
SELECT id FROM countries WHERE name = @country
GO

CREATE PROCEDURE [dbo].[sp_get_export_files]
@exportID as int
AS
	SELECT * FROM export_files WHERE export_id_fk = @exportID
GO

CREATE PROCEDURE [dbo].[sp_get_export]
@exportID as int
AS
	SELECT * FROM exports WHERE id = @exportID
GO

CREATE PROCEDURE [dbo].[sp_get_exports]
@interviewID as int
AS
	SELECT * FROM exports WHERE interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_get_interview]
@ID as int
AS
	SELECT id, casefile_id_fk as casefileID, interview_type_id_fk as interviewTypeID, location_id_fk as locationID, interview_date as interviewDate, notes, private
	FROM interviews
	WHERE id = @ID
GO

CREATE PROCEDURE [dbo].[sp_get_interview_attendees]
@interviewID as int
AS
	SELECT p.id, p.lastname, p.firstname, p.section_id_fk as sectionID, p.phone, p.country_id_fk as countryID, p.province_id_fk as provinceID, p.notes
	FROM personnel p, interview_attendees i, personnel_type pt
	WHERE p.id = i.personnel_id_fk AND  p.type_id_fk = pt.id and pt.description = 'investigator'  and i.interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_get_interview_footage]
@interviewID as int,
@footageType as int = 0
AS
	IF @footageType = 0
	BEGIN
		SELECT id, interview_id_fk as interviewID, footage_type_id_fk as footageTypeID,  start_time as startTime, end_time as endTime, footage_folder as folder, filename, notes  FROM interview_footage WHERE interview_id_fk = @interviewID
	END
	ELSE
	BEGIN
		SELECT id, interview_id_fk as interviewID, footage_type_id_fk as footageTypeID,  start_time as startTime, end_time as endTime, footage_folder as folder, filename, notes  FROM interview_footage WHERE interview_id_fk = @interviewID AND footage_type_id_fk = @footageType
	END
GO

CREATE PROCEDURE [dbo].[sp_get_interview_subjects]
@interviewID as int
AS
	SELECT s.id, s.lastname, s.firstname, s.DOB, s.gender, s.address, s.city, s.country_id_fk as countryID, s.province_id_fk as provinceID, s.postal, s.phone1, s.phone2, s.notes
			 FROM subjects s, interview_subjects i
			 WHERE s.id = i.subject_id_fk AND i.interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_get_interview_transcribers]
@interviewID as int
AS
	SELECT p.id, p.lastname, p.firstname, p.section_id_fk as sectionID, p.phone, p.country_id_fk as countryID, p.province_id_fk as provinceID, p.notes
	FROM personnel p, interview_transcribers i, personnel_type pt
	WHERE p.id = i.personnel_id_fk AND  p.type_id_fk = pt.id and pt.description = 'transcriber'  and i.interview_id_fk = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_get_interview_type]
@typeID as int = 0
AS
	SELECT *
	FROM interview_types
	WHERE id = @typeID
	OR @typeID = 0
GO

CREATE PROCEDURE [dbo].[sp_get_location]
@locationID as int = 0
AS
	SELECT *
	FROM locations
	WHERE id = @locationID
	OR @locationID = 0
	ORDER by description
GO

CREATE PROCEDURE [dbo].[sp_get_offenses]
@offenseID as int = 0
AS
	SELECT *
	FROM offenses
	WHERE id = @offenseID
	OR @offenseID = 0
GO

CREATE PROCEDURE [dbo].[sp_get_personal_info]
@ID as int
 AS
	SELECT id, lastname, firstname, type_id_fk as type, section_id_fk as sectionID, phone, country_id_fk as countryID, province_id_fk as provinceID, notes, username, password, admin, department, regimental, password_version
	FROM personnel
	WHERE id = @ID
GO

CREATE PROCEDURE [dbo].[sp_get_personnel]
@personnelType as int = 0
AS
	SELECT p.id, p.lastname, p.firstname, p.type_id_fk as type, p.section_id_fk as sectionID, p.phone, p.country_id_fk as countryID, p.province_id_fk as provinceID, p.notes, p.admin, p.username, p.password, p.department, p.regimental, p.password_version
	FROM personnel p, personnel_type pt
	WHERE p.type_id_fk = pt.id and (pt.id = @personnelType OR @personnelType = 0)
	ORDER BY p.lastname
GO

CREATE PROCEDURE [dbo].[sp_get_provinces]
@countryID as int
AS
	SELECT * FROM provinces WHERE country_id_fk = @countryID  ORDER BY name
GO

CREATE PROCEDURE [dbo].[sp_get_recorder]
@recorderID as int = 0
AS
	SELECT *
	FROM recorders
	WHERE id = @recorderID
	OR @recorderID = 0
GO

CREATE PROCEDURE [dbo].[sp_get_assigned_recorders]
@locationID as int = 0
AS
	SELECT *
	FROM recorders
	WHERE location_id_fk = @locationID
GO

CREATE PROCEDURE [dbo].[sp_get_section]
@nSectionID as int = 0
AS
	SELECT *
	FROM sections
	WHERE id = @nSectionID
	OR @nSectionID = 0
GO

CREATE PROCEDURE [dbo].[sp_get_subject]
@subjectID as int
AS
	SELECT id, lastname, firstname, dob, gender, address, city, country_id_fk as countryID, province_id_fk as provinceID, postal, phone1, phone2, notes
	FROM subjects
	WHERE id = @subjectID
GO

CREATE PROCEDURE [dbo].[sp_get_personnel_types]
AS
	SELECT *
	FROM personnel_type
GO

CREATE PROCEDURE [dbo].[sp_get_user]
@user as varchar(20),
@pass as varchar(50)
AS
	SELECT id, lastname, firstname, type_id_fk as type, section_id_fk as sectionID, phone, country_id_fk as countryID, province_id_fk as provinceID, notes, admin, username, password, department, regimental, password_version
	FROM personnel
	WHERE username = @user
	AND ((password_version = 0 AND password = SUBSTRING(@pass, 1, LEN(password))) OR (password_version > 0 AND password = @pass))
GO

CREATE PROCEDURE [dbo].[sp_get_admin_count]
AS
	SELECT count(id) AS admin_count
	FROM personnel
	WHERE NOT username IS NULL
	AND admin = 1
GO

CREATE PROCEDURE [dbo].[sp_insert_casefile]
@fileNo as varchar(20),
@offenseID as int,
@sectionID as int,
@notes as varchar(1000),
@private as bit
AS
	SET NOCOUNT ON

	INSERT INTO casefiles VALUES(@fileNo, @offenseID, @sectionID, @notes, GETDATE(), @private)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_casefile_investigator]
@fileID as int,
@investigatorID as int
AS
	SET NOCOUNT ON

	INSERT INTO casefile_investigators VALUES(@fileID, @investigatorID)

	SELECT @@identity as new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_export]
@interviewID as int,
@personnelID as int,
@exportTime as datetime,
@exportTypeID as int,
@path as varchar(256),
@reason as varchar(300),
@copies as int
AS
	SET NOCOUNT ON

	INSERT INTO exports VALUES(@interviewID, @personnelID, @exportTime, @exportTypeID, @path, @reason, @copies)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_export_file]
@exportID as int,
@file as varchar(256)
AS
	SET NOCOUNT ON

	INSERT INTO export_files VALUES(@exportID, @file)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_interview]
@casefileID as int,
@interviewTypeID as int,
@locationID as int,
@interviewDate as datetime,
@notes as varchar(1000),
@private as bit
AS
	SET NOCOUNT ON

	INSERT INTO interviews VALUES(@casefileID, @interviewTypeID, @locationID, @interviewDate, @notes, @private)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_interview_attendee]
@interviewID as int,
@attendeeID as int
AS
	SET NOCOUNT ON

	INSERT INTO interview_attendees VALUES(@interviewID, @attendeeID)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_interview_footage]
@interviewID as int,
@footageTypeID as int,
@startTime as datetime,
@endTime as datetime,
@folder as varchar(250),
@filename as varchar(250),
@notes as varchar(1000)
AS
	SET NOCOUNT ON
	
	INSERT INTO interview_footage VALUES(@interviewID, @footageTypeID, @startTime, @endTime, @folder, @filename, @notes)
	
	SELECT @@identity as new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_interview_subject]
@interviewID as int,
@subjectID as int
AS
	SET NOCOUNT ON

	INSERT INTO interview_subjects  VALUES(@interviewID, @subjectID)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_interview_transcriber]
@interviewID as int,
@transcriberID as int
AS
	SET NOCOUNT ON

	INSERT INTO interview_transcribers VALUES(@interviewID, @transcriberID)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_location]
@description as varchar(50)
AS

	SET NOCOUNT ON

	INSERT INTO locations (description) VALUES (@description)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_offense]
@offense as nvarchar(50)
AS
	SET NOCOUNT ON

	INSERT
	INTO offenses
	VALUES(@offense)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_personnel]
@lastName as varchar(50),
@firstName as varchar(50),
@typeID as int,
@sectionID as int,
@phone as varchar(20),
@countryID as int,
@provinceID as int,
@notes as varchar(300),
@username as varchar(20),
@password as varchar(50),
@admin as bit,
@department as varchar(200),
@regimental as varchar(20),
@passwordVersion as int
AS
	SET NOCOUNT ON

	INSERT INTO personnel
	(lastname, firstname, type_id_fk, section_id_fk, phone, country_id_fk, province_id_fk, notes, username, password, admin, department, regimental, password_version)
	VALUES
	(@lastName, @firstName, @typeID, @sectionID, @phone, @countryID, @provinceID, @notes, @username, @password, @admin, @department, @regimental, @passwordVersion)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_recorder]
@locationID as int,
@IPaddress as varchar(50),
@PortNo as int,
@description as varchar(50),
@DVRType as int,
@UserName as varchar(50),
@PassWord as varchar(50)
AS
	SET NOCOUNT ON

	INSERT INTO recorders (location_id_fk, IP, port, description, dvr_type, username, password) VALUES(@locationID, @IPaddress, @PortNo, @description, @DVRType, @UserName, @PassWord)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_section]
@description as varchar(50)
AS
	SET NOCOUNT ON

	INSERT INTO sections (description) VALUES (@description)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_interview_type]
@description as varchar(50)
AS

	SET NOCOUNT ON

	INSERT INTO interview_types (description) VALUES (@description)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_insert_subject]
@lastName as varchar(50),
@firstName as varchar(100),
@dob as datetime,
@gender as char,
@address as varchar(100),
@city as varchar(50),
@countryID as int,
@provinceID as int,
@postal as varchar(10),
@phone1 as varchar(20),
@phone2 as varchar(20),
@notes as varchar(1000)
AS
	SET NOCOUNT ON

	INSERT INTO subjects VALUES(@lastName, @firstName, @dob, @gender, @address, @city, @countryID, @provinceID, @postal, @phone1, @phone2, @notes)

	SELECT @@identity AS new_id
GO

CREATE PROCEDURE [dbo].[sp_is_person_busy]
@personnelID as int
AS
	SET NOCOUNT ON

	declare @count as int
	declare @busy as bit

	SELECT @count = COUNT(id) from casefile_investigators WHERE personnel_id_fk = @personnelID

	IF @count = 0
	BEGIN
		SELECT @count = COUNT(id) from interview_transcribers WHERE personnel_id_fk = @personnelID
	END

	IF @count = 0
	BEGIN
		SELECT @count = COUNT(id) from interview_attendees WHERE personnel_id_fk = @personnelID
	END

	IF @count > 0
	BEGIN
		SET @busy = 1
	END
	ELSE
	BEGIN
		SET @busy = 0
	END

	SELECT @busy as IsBusy
GO

CREATE PROCEDURE [dbo].[sp_is_subject_busy] 
@id as int
AS
	declare @count as int
	declare @busy as bit

	select @count = count(id) from interview_subjects where subject_id_fk = @id

	if @count > 0
	begin
		set @busy = 1
	end
	else
	begin
		set @busy = 0
	end

	select @busy as IsBusy
GO

CREATE PROCEDURE [dbo].[sp_is_section_busy] 
@id as int
AS
	declare @count as int
	declare @busy as bit

	select @count = count(id) from casefiles where section_id_fk = @id

	if @count > 0
	begin
		set @busy = 1
	end
	else
	begin
		set @busy = 0
	end

	select @busy as IsBusy
GO

CREATE PROCEDURE [dbo].[sp_is_interview_type_busy] 
@id as int
AS
	declare @count as int
	declare @busy as bit

	select @count = count(id) from interviews where interview_type_id_fk = @id

	if @count > 0
	begin
		set @busy = 1
	end
	else
	begin
		set @busy = 0
	end

	select @busy as IsBusy
GO

CREATE PROCEDURE [dbo].[sp_is_offense_busy] 
@id as int
AS
	declare @count as int
	declare @busy as bit

	select @count = count(id) from casefiles where offense_id_fk = @id

	if @count > 0
	begin
		set @busy = 1
	end
	else
	begin
		set @busy = 0
	end

	select @busy as IsBusy
GO

CREATE PROCEDURE [dbo].[sp_is_location_busy] 
@id as int
AS
	declare @count as int
	declare @busy as bit

	select @count = count(id) from interviews where location_id_fk = @id

	if @count > 0
	begin
		set @busy = 1
	end
	else
	begin
		set @busy = 0
	end

	select @busy as IsBusy
GO

CREATE PROCEDURE [dbo].[sp_is_unique_fileno]
@fileNo as varchar(20)
AS
	SET NOCOUNT ON

	declare @count as int
	declare @unique as bit

	SELECT @count = COUNT(id) from casefiles  WHERE file_no = @fileNo

	IF @count = 0
	BEGIN
		SET @unique = 1
	END
	ELSE
	BEGIN
		SET @unique = 0
	END

	SELECT @unique as IsUnique
GO

CREATE PROCEDURE sp_prune_audit
@maxRecordCount INT = 10000
AS
	SET NOCOUNT ON
	
	DECLARE @deleteCount INT

	DECLARE @numRecords INT

	SELECT @numRecords = count(id) from audit

	SET @deleteCount = @numRecords - @maxRecordCount

	IF @deleteCount > 0
	BEGIN
		SET ROWCOUNT @deleteCount
		
		DELETE FROM audit

		SET ROWCOUNT 0
	END
	ELSE
	BEGIN
		SET @deleteCount = 0
	END
	
	SELECT @deleteCount AS DeleteCount
GO

CREATE PROCEDURE [dbo].[sp_touch_casefile] 
@casefileID as int
AS
	UPDATE casefiles SET last_updated = GETDATE() WHERE id = @casefileID
GO

CREATE PROCEDURE [dbo].[sp_update_casefile]
@casefileID as int,
@fileNo as varchar(20),
@offenseID as int,
@sectionID as int,
@notes as varchar(1000),
@private as bit
AS
	UPDATE casefiles
	SET file_no = @fileNo, 
	offense_id_fk = @offenseID,
	section_id_fk = @sectionID,
	notes = @notes,
	private = @private,
	last_updated = GETDATE()
	WHERE id = @casefileID
GO

CREATE PROCEDURE [dbo].[sp_update_interview]
@interviewID as int,
@casefileID as int,
@interviewTypeID as int,
@locationID as int,
@interviewDate as datetime,
@notes as varchar(1000),
@private as bit
AS
	UPDATE interviews
	SET casefile_id_fk = @casefileID,
	interview_type_id_fk = @interviewTypeID,
	location_id_fk = @locationID,
	interview_date = @interviewDate,
	notes = @notes,
	private = @private
	WHERE id = @interviewID
GO

CREATE PROCEDURE [dbo].[sp_update_interview_footage]
@footageID as int,
@interviewID as int,
@footageTypeID as int,
@startTime as datetime,
@endTime as datetime,
@folder as varchar(250),
@filename as varchar(250),
@notes as varchar(1000)
AS
	UPDATE interview_footage 
	SET interview_id_fk = @interviewID, 
	footage_type_id_fk = @footageTypeID, 
	start_time = @startTime, 
	end_time =@endTime, 
	footage_folder = @folder, 
	filename = @filename, 
	notes = @notes
	WHERE id = @footageID
GO

CREATE PROCEDURE [dbo].[sp_update_location]
@ID as int,
@description as varchar(50)
AS
	UPDATE locations
	SET description = @description
	WHERE id = @ID
GO

CREATE PROCEDURE [dbo].[sp_update_personnel]
@ID as int,
@lastName as varchar(50),
@firstName as varchar(50),
@typeID as int,
@sectionID as int,
@phone as varchar(20),
@countryID as int,
@provinceID as int,
@notes as varchar(300),
@username as varchar(20),
@password as varchar(50),
@admin as bit,
@department as varchar(200),
@regimental as varchar(20),
@passwordVersion as int
AS
	UPDATE personnel
	SET lastname = @lastName,
	firstname = @firstName,
	type_id_fk = @typeID,
	section_id_fk = @sectionID,
	phone = @phone,
	country_id_fk = @countryID,
	province_id_fk = @provinceID,
	notes = @notes,
	username = @username,
	password = @password,
	admin = @admin,
	department = @department,
	regimental = @regimental,
	password_version = @passwordVersion
	WHERE id = @ID
GO

CREATE PROCEDURE [dbo].[sp_update_recorder]
@recorderID as int,
@locationID as int,
@IPaddress as varchar(50),
@portNo as int,
@description as varchar(50),
@DVRType as int,
@UserName as varchar(50),
@PassWord as varchar(50)
AS
	UPDATE recorders
	SET  IP = @IPaddress,
	location_id_fk = @locationID,
	port = @portNo,
	description = @description,
	dvr_type = @DVRType,
	username = @UserName,
	password = @PassWord
	WHERE id = @recorderID
GO

CREATE PROCEDURE [dbo].[sp_update_section]
@sectionID as int,
@description as varchar(50)
AS
	UPDATE sections
	SET description = @description
	WHERE id = @sectionID
GO

CREATE PROCEDURE [dbo].[sp_update_interview_type]
@interviewTypeID as int,
@description as varchar(50)
AS
	UPDATE interview_types
	SET description = @description
	WHERE id = @interviewTypeID
GO

CREATE PROCEDURE [dbo].[sp_update_offense]
@offenseID as int,
@description as varchar(50)
AS
	UPDATE offenses
	SET description = @description
	WHERE id = @offenseID
GO

CREATE PROCEDURE [dbo].[sp_update_subject]
@subjectID as int,
@lastName as varchar(50),
@firstName as varchar(100),
@dob as datetime,
@gender as char,
@address as varchar(100),
@city as varchar(50),
@countryID as int,
@provinceID as int,
@postal as varchar(10),
@phone1 as varchar(20),
@phone2 as varchar(20),
@notes as varchar(1000)
AS
	UPDATE subjects
	SET lastname = @lastName,
	firstname = @firstName,
	dob = @dob,
	gender = @gender,
	address = @address,
	city = @city,
	country_id_fk = @countryID,
	province_id_fk = @provinceID,
	postal = @postal,
	phone1 = @phone1,
	phone2 = @phone2,
	notes = @notes
	WHERE id = @subjectID
GO

CREATE PROCEDURE [dbo].[sp_delete_interview]
@interviewID as int
AS
	DELETE FROM interviews WHERE id = @interviewID
	EXEC sp_delete_interview_subjects @interviewID
	EXEC sp_delete_interview_footage @interviewID
	EXEC sp_delete_interview_attendees @interviewID
	EXEC sp_delete_interview_transcribers @interviewID
GO
