<?xml version="1.0" encoding="utf-8" ?>

<dbscripts>
	<!--<script>
		<name>Alter Delete MasterDelete Procedure</name>
		<sql>
			ALTER Procedure [dbo].[DeleteMasterData]
			/*
			(
			@parameter1 int = 5,
			@parameter2 datatype OUTPUT
			)

			*/
			AS

			Begin Tran
			delete from dbo.mActivityInfo
			delete from dbo.mAssessmentInfo
			delete from dbo.mATCInfo
			delete from dbo.mBodyAreaInfo
			delete from dbo.mBodyPartInfo
			delete from dbo.MedRefOrthoFacilityTable
			delete from dbo.MedRefSourceAPTable
			delete from dbo.mInjuryInfo
			delete from dbo.mLocationInfo
			delete from dbo.mMedicalRef_Location
			delete from dbo.mOnsetInfo
			delete from dbo.mPhyscianInfo
			delete from dbo.mSiteInfo
			delete from dbo.PlanRecActivityLevelTable
			delete from dbo.PlanRecLimitRestrictions
			delete from dbo.PlanRecLimitRestrictionsTable
			delete from dbo.PlanRecMgmtRecommendationsTable
			delete from dbo.PlanRecMgmtRecTable
			delete from dbo.StudentGradeTable
			delete from dbo.SurgicalProcHospitalTable
			delete from dbo.SurgicalProcRehabTable
			delete from dbo.SurgicalProcTable
			DELETE FROM dbo.MedRefTypeTable
			DELETE FROM dbo.mOnsetInfo
			DELETE FROM dbo.mEvalInjuryTypeClassification
			Commit

		</sql>
	   </script>
	<script>
		<name>Create  [HBE_spPopulateCoachesReportPriorEval]</name>
		<sql>
			Create     Proc [dbo].[HBE_spPopulateCoachesReportPriorEval]
			(@EvalDate DATETIME, @SchoolName VARCHAR(50), @Sport VARCHAR(50), @EvalID UNIQUEIDENTIFIER )

			AS
			set nocount on

			truncate table dbo.RptCouchesTable


			insert into dbo.RptCouchesTable

			/* Changed per request 4/15/2005'
			select
			ie.ATCID, atc.FullName, atc.PhoneNumber, atc.EmailAddr,
			ie.SchoolList, ie.Sport, ie.EvaluationDate,
			ie.FName  + ' ' + ie.LName as FullName, left(ie.LRB,1), ie.BodyPart,
			ie.Classification, ie.Assessment, pr.ManagementRecommendations,
			pr.ActivityLevel, pr.Limit_Restrictions, pr.Limit_RestrictionDesc,
			pr.TreatmentTherapy,
			pr.Protection_Tape + ' ' + pr.Protection_Brace + ' '+ pr.Protection_Pad + ' ' + pr.Protection_Splint + ' ' AS ProtectionSupport,
			pr.Comments, 'sa'
			from
			dbo.uInjuryEvaluation ie inner join  dbo.mATCInfo atc on ie.ATCID = atc.ATCID inner join dbo.uPlanRecommendations pr on ie.evaluationid = pr.evaluationid
			where
			ie.EvaluationDate = @EvalDate and ie.SchoolList = @SchoolName and ie.Sport = @Sport
			*/
			select distinct
			ie.ATCID, atc.FullName, atc.PhoneNumber, atc.EmailAddr,
			ie.SchoolList, sp.InjuryName, ie.EvaluationDate,
			ie.FName  + ' ' + ie.LName as FullName, left(ie.LRB,1), ie.BodyPart,
			ie.Classification, ie.Assessment, pr.ManagementRecommendations,
			pr.ActivityLevel, pr.Limit_Restrictions, pr.Limit_RestrictionDesc,
			pr.TreatmentTherapy,
			pr.Protection_Tape + ' ' + pr.Protection_Brace + ' '+ pr.Protection_Pad + ' ' + pr.Protection_Splint + ' ' AS ProtectionSupport,
			pr.Comments, 'sa',
			ie.evaluationid,
			ie.Site
			from
			dbo.uInjuryEvaluation ie inner join  dbo.mATCInfo atc on ie.ATCID = atc.ATCID inner join dbo.uPlanRecommendations pr on ie.evaluationid = pr.evaluationid inner join dbo.mInjuryInfo sp on ie.sport = sp.injuryid AND sp.Gender = ie.Gender
			where
			ie.EvaluationDate = @EvalDate and ie.SchoolList = @SchoolName and ie.Sport = @Sport
			AND ie.EvaluationID = @EvalID
			order by fullname
		</sql>
	   </script>
		  <script>
		<name>Alter RptAthleticDirectorTable </name>
			<sql>
				ALTER TABLE dbo.RptAthleticDirectorTable
				ADD Limit_RestrictionDesc VARCHAR(255) NULL
			</sql>			  
	   </script>
	<script>
		<name>Alter Procedure qproc_InsertDirectorReport</name>
		<sql>
			ALTER Proc [dbo].[qproc_InsertDirectorReport]
			(@BegDate DateTime, @EndDate DateTime, @SchoolName Varchar(50))

			AS
			Declare @ReportDate DateTime
			Set @ReportDate = GetDate()

			Truncate Table dbo.RptAthleticDirectorTable
			
			Insert Into dbo.RptAthleticDirectorTable
			(
			ReportDate,
			ATCInfo,
			ATCCell,
			StudentFirstName,
			StudentLastName,
			StudentName,
			SchoolName,
			SportID,
			Body,
			BodyPart,
			Site,
			Assessment,
			InjuryTypeClassification,
			MedicalReferral,
			EvulationDate,
			ManagementRecommendations,
			ActivityLevel,
			LimitAndRestrictions,
			Limit_RestrictionDesc,
			RecordID
			)
			Select distinct
			GetDate() as ReportDate,
			a.FullName as ATCInfo,
			a.PhoneNumber as ATCCell,
			fe.FName as StudentFirstName,
			fe.LName as StudentLastName,
			fe.FName + ' ' + fe.LName as StudentName,
			fe.SchoolList as SchoolName,
			fe.Sport as SportID,
			left(fe.LRB,1) as Body,
			fe.BodyPart as BodyPart,
			fe.Site as Site,
			fe.Assessment as Assessment,
			fe.Classification as InjuryTypeClassification,
			case
			when len(fe.SourceAP) > 0 then 'X'
			else ' '
			end
			as MedicalReferral,
			fe.EvaluationDate as EvaluationDate,
			fe.ManagementRecommendations,
			fe.ActivityLevel,
			fe.Limit_Restrictions,
			fe.Limit_RestrictionDesc,
			fe.RecordID as RecordID
			from
			dbo.vFullEvaluation fe
			join dbo.mATCInfo a on fe.ATCID = a.ATCID
			join (select max(recordID) as RecordID, studentID, injuryID from dbo.uInjuryEvaluation where EvalComplete = 1 group by StudentID, InjuryID) e on fe.RecordID = e.RecordID
			Where
			EvaluationDate Between @BegDate and @EndDate
			and fe.SchoolList = @SchoolName
			Order by SchoolName, StudentLastName, fe.EvaluationDate
		</sql>
	   </script>-->
	<!--	<script>
		<name>Create Prior Eval List Proc</name>
		<sql>
			CREATE PROC [dbo].[fnd_pPriorEvalListSelect]
			(@student_id AS UNIQUEIDENTIFIER, @injury_id AS UNIQUEIDENTIFIER)
			AS
			SELECT
			EvaluationDate,SchoolList, Sport,EvaluationID
			FROM
			dbo.uInjuryEvaluation
			WHERE
			StudentID = @student_id
			AND InjuryID = @injury_id
			AND EvalComplete =1
			ORDER BY
			EvaluationDate DESC
		</sql>
	   </script>
	<script>
		<name>Update DeleteMasterData store procedure</name>
		<sql>
			ALTER Procedure [dbo].[DeleteMasterData]
			/*
			(
			@parameter1 int = 5,
			@parameter2 datatype OUTPUT
			)

			*/
			AS

			Begin Tran
			delete from dbo.mActivityInfo
			delete from dbo.mAssessmentInfo
			delete from dbo.mATCInfo
			delete from dbo.mBodyAreaInfo
			delete from dbo.mBodyPartInfo
			delete from dbo.MedRefOrthoFacilityTable
			delete from dbo.MedRefSourceAPTable
			delete from dbo.mInjuryInfo
			delete from dbo.mLocationInfo
			delete from dbo.mMedicalRef_Location
			delete from dbo.mOnsetInfo
			delete from dbo.mPhyscianInfo
			delete from dbo.mSiteInfo
			delete from dbo.PlanRecActivityLevelTable
			delete from dbo.PlanRecLimitRestrictions
			delete from dbo.PlanRecLimitRestrictionsTable
			delete from dbo.PlanRecMgmtRecommendationsTable
			delete from dbo.PlanRecMgmtRecTable
			delete from dbo.StudentGradeTable
			delete from dbo.SurgicalProcHospitalTable
			delete from dbo.SurgicalProcRehabTable
			delete from dbo.SurgicalProcTable
			DELETE FROM dbo.MedRefTypeTable
			Commit
		</sql>
	   </script>
	<script>
		<name>Alter Tables</name>
		<sql>
			ALTER TABLE dbo.uInjuryEvaluation
			ADD ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()

			ALTER TABLE dbo.mStudentInfo
			ADD ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()


			ALTER TABLE dbo.StudentInjuryTable
			ADD ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
		</sql>
	</script>
	SCRIPT UPDATE 9/16/2014 to Handle Remote Sync Version 2014.09.15.03
		<script>
			<name>Alter mStudentInfo</name>
			<sql>
				alter table dbo.mStudentInfo
				add LastSyncDate DateTime not null default getdate()
			</sql>
		</script>
		<script>
			<name>Alter uInjuryEvaluation</name>
			<sql>
				alter table dbo.uInjuryEvaluation
				add LastSyncDate DateTime not null default getdate()
			</sql>
		</script>
		<script>
			<name>Alter StudentInjuryTable</name>
			<sql>
				alter table dbo.StudentInjuryTable
				add LastSyncDate DateTime not null default getdate()
			</sql>
		</script>
		<script>
			<name>Update mStudentInfo</name>
			<sql>
				update dbo.mStudentInfo set LastSyncDate = ModifiedDate
			</sql>
		</script>
		<script>
			<name>Update uInjuryEvaluation</name>
			<sql>
				update dbo.uInjuryEvaluation set LastSyncDate = ModifiedDate
			</sql>
		</script>
		<script>
			<name>Update StudentInjuryTable</name>
			<sql>
				update dbo.StudentInjuryTable set LastSyncDate = ModifiedDate
			</sql>
		</script>
		<script>
			<name>Update Delete Data Stored Procedure</name>
			<sql>
				ALTER Procedure [dbo].[DeleteMasterData]
				/*
				(
				@parameter1 int = 5,
				@parameter2 datatype OUTPUT
				)

				*/
				AS

				Begin Tran
				delete from dbo.mActivityInfo
				delete from dbo.mAssessmentInfo
				delete from dbo.mATCInfo
				delete from dbo.mBodyAreaInfo
				delete from dbo.mBodyPartInfo
				delete from dbo.MedRefOrthoFacilityTable
				delete from dbo.MedRefSourceAPTable
				delete from dbo.mInjuryInfo
				delete from dbo.mLocationInfo
				delete from dbo.mMedicalRef_Location
				delete from dbo.mOnsetInfo
				delete from dbo.mPhyscianInfo
				delete from dbo.mSiteInfo
				delete from dbo.PlanRecActivityLevelTable
				delete from dbo.PlanRecLimitRestrictions
				delete from dbo.PlanRecLimitRestrictionsTable
				delete from dbo.PlanRecMgmtRecommendationsTable
				delete from dbo.PlanRecMgmtRecTable
				delete from dbo.StudentGradeTable
				delete from dbo.SurgicalProcHospitalTable
				delete from dbo.SurgicalProcRehabTable
				delete from dbo.SurgicalProcTable
				DELETE FROM dbo.MedRefTypeTable
				DELETE FROM dbo.mEvalInjuryTypeClassification
				Commit
			</sql>
		</script>-->

	<!--<script>
		<name>Alter Student Injury</name>
		<sql>
			ALTER Proc [dbo].[HBE_spCreateInjuryRecord]

			(
			@StudentID UNIQUEIDENTIFIER,
			@Assessment VARCHAR(255),
			@TypeClassification VARCHAR(255),
			@InjuryID UniqueIdentifier
			)

			AS
			if (Select Count(*) from dbo.StudentInjuryTable Where InjuryID = @InjuryID) >0

			BEGIN
			Update dbo.StudentInjuryTable
			Set Assessment = @Assessment, TypeClassification = @TypeClassification, ModifiedDate = GETDATE()
			Where StudentID = @StudentID and InjuryID = @InjuryID

			UPDATE dbo.mStudentInfo
			SET ModifiedDate = GETDATE()
			WHERE StudentID = @StudentId
			END
			else
			BEGIN
			Insert Into dbo.StudentInjuryTable
			(StudentID, Assessment, TypeClassification, InjuryID, ModifiedDate)
			VALUES
			(@StudentID, @Assessment, @TypeClassification, @InjuryID, GETDATE())


			UPDATE dbo.mStudentInfo
			SET ModifiedDate = GETDATE()
			WHERE StudentID = @StudentId
			END
		</sql>
	</script>
	<script>
		<name>Alter Student Record</name>
		<sql>
			ALTER   PROCEDURE [dbo].[HBE_pInsertStudentInfo]
			@StudentID UNIQUEIDENTIFIER ,
			@FName VARCHAR(255) ,
			@LName VARCHAR(255) ,
			@FullName VARCHAR(510) ,
			@Age INT ,
			@Grade VARCHAR(50) ,
			@Gender CHAR(1) ,
			@DateOfBirth DATETIME ,
			@SchoolName VARCHAR(255)
			AS /* $Revision $ */

			DECLARE
			@intRetCode INT ,
			@intRowcount INT ,
			@intIdentity INT ,
			@intError INT

			SELECT
			@intRetCode = 0


			DECLARE @SchoolID UNIQUEIDENTIFIER

			SELECT TOP 1
			@SchoolID = SCHOOLID
			FROM
			dbo.mSchoolInfo
			WHERE
			SchoolName = @SchoolName

			IF ( SELECT
			COUNT(*)
			FROM
			dbo.mStudentInfo
			WHERE
			StudentID = @StudentID
			) > 0
			BEGIN

			UPDATE
			dbo.mStudentInfo
			SET
			FName = @FName ,
			LName = @LName ,
			FullName = @FullName ,
			Age = @Age ,
			Grade = @Grade ,
			Gender = @Gender ,
			DateOfBirth = @DateOfBirth ,
			SchoolID = @SchoolID ,
			RecUpdated = 1,
			ModifiedDate = GETDATE()
			WHERE
			StudentID = @StudentID

			END

			ELSE
			BEGIN
			INSERT  mStudentInfo
			( StudentID ,
			FName ,
			LName ,
			FullName ,
			Age ,
			Grade ,
			Gender ,
			DateOfBirth ,
			SchoolID ,
			RecUpdated,
			ModifiedDate
			)
			SELECT
			@StudentID ,
			@FName ,
			@LName ,
			@FullName ,
			@Age ,
			@Grade ,
			@Gender ,
			@DateOfBirth ,
			@SchoolID ,
			1,
			GETDATE()
			END
		</sql>
	</script>
	<script>
		<name>Create Split Function</name>
		<sql>
			CREATE FUNCTION [dbo].[fnSplit]
			(
			@List VARCHAR(MAX) ,
			@SplitOn VARCHAR(5)
			)
			RETURNS @RtnValue TABLE
			(
			Id INT IDENTITY(1, 1) ,
			Value NVARCHAR(256)
			)
			AS
			BEGIN
			WHILE ( CHARINDEX(@SplitOn, @List) > 0 )
			BEGIN
			INSERT  INTO @RtnValue
			( value
			)
			SELECT  Value = LTRIM(RTRIM(SUBSTRING(@List, 1,
			CHARINDEX(@SplitOn,
			@List) - 1)))
			SET @List = SUBSTRING(@List,
			CHARINDEX(@SplitOn, @List)
			+ LEN(@SplitOn), LEN(@List))
			END

			INSERT  INTO @RtnValue
			( Value )
			SELECT  Value = LTRIM(RTRIM(@List))

			RETURN
			END
		</sql>
	</script>
	<script>
		<name>Create Missing Plan Recommendations</name>
		<sql>
			CREATE PROC dbo.pInsertMissingPlanRecommendations_TreatmentThrapy
			(@EvalID UNIQUEIDENTIFIER, @StudentID UNIQUEIDENTIFIER, @InjuryID UNIQUEIDENTIFIER,@TreatmentTherapyValues VARCHAR(MAX))
			AS

			INSERT INTO dbo.uPlanRecommendations_TreatmentThrapy
			( EvaluationID ,
			TreatmentTherapy ,
			StudentID ,
			InjuryID ,
			RecUpdated
			)

			SELECT
			@EvalId,
			Value,
			@StudentID,
			@InjuryId,
			1

			FROM
			[dbo].[fnSplit](@TreatmentTherapyValues,';')
			WHERE
			Value != ''
		</sql>
	</script>-->
	<!--<script>
		<name></name>
		<sql></sql>
	</script>-->
	
	<!--VERSION 2015.05.01 -->
	<script>
		<name>Update LRB values "R"</name>
		<sql>
			UPDATE DBO.uInjuryEvaluation
			SET LRB = 'R'
			WHERE LRB = 'R-Right'
		</sql>
	</script>
	<script>
		<name>Update LRB values "L"</name>
		<sql>
			UPDATE DBO.uInjuryEvaluation
			SET LRB = 'L'
			WHERE LRB = 'L-Left'
		</sql>
	</script>
	<script>
		<name>Update LRB values "B"</name>
		<sql>
			UPDATE DBO.uInjuryEvaluation
			SET LRB = 'B'
			WHERE LRB = 'B-Bilateral'
		</sql>
	</script>
	<script>
		<name>Update LRB values "NA" Head</name>
		<sql>
			UPDATE dbo.uInjuryEvaluation
			SET LRB = 'NA'
			where BodyPart = 'Head'
		</sql>
	</script>
	<script>
		<name>Update LRB values "NA" Brain</name>
		<sql>
			UPDATE dbo.uInjuryEvaluation
			SET LRB = 'NA'
			where BodyPart = 'Brain'
		</sql>
	</script>
	<script>
		<name>Add New Fields to Injury Evaluation</name>
		<sql>
			Alter Table dbo.uInjuryEvaluation
			add
			[ActivityType_InSeason] bit default 0
			,[ActivityType_PEClass] bit default 0
			,[ActivityType_ClubSport] bit default 0
		</sql>
	   </script>
	<script>
		<name>Update PEClass/ClubSport/InSeason - Eval2</name>
		<sql>
			ALTER Procedure [dbo].[uproc_SelectInjuryEval2]
			/ Param List /
			(
			@StudentID UniqueIdentifier,
			@InjuryID UniqueIdentifier,
			@EvalID UniqueIdentifier
			)
			AS

			/**
			File: Name: uproc_SelectInjuryEval
			Desc:
			This template can be customized:
			Return values:
			Called by:
			Parameters: Input Output

			Auth:
			** Date:

			** Change History

			** Date: Author: Description:


			*/

			SELECT TOP 1
			[EvaluationID],
			[EvaluationDate],
			[ATCID],
			[Episode],
			[OnSetDate],
			[OnsetOther],
			[NatureOfOnset],
			[PriorHistory],
			[ActivityType_Practice],
			[ActivityType_Competition],
			[ActivityType_Conditioning],
			[ActivityType_WeightLifting],
			[ActivityType_Recreational],
			[ActivityType_NonAthletic],
			[ActivityType_OffSeason],
			[ActivityType_InSeason],
			[ActivityType_ClubSport] ,
			[ActivityType_PEClass] ,
			[Location],
			[SubComments],
			[FName],
			[LName],
			[Age],
			[Gender],
			[Grade],
			[ParentEmailAddr],
			[SchoolList],
			[Sport],
			[LRB],
			[BodyArea],
			[BodyPart],
			[Site],
			[Classification],
			[Assessment],
			[ObjComments],
			[RecordDate],
			[RecordID],
			[InjuryID],
			[StudentID],
			[EvalComplete]
			FROM
			[dbo].[uInjuryEvaluation]
			WHERE
			StudentID = @StudentID AND
			InjuryID = @InjuryID AND
			EvaluationID = @EvalID
		</sql>
	</script>
	<script>
		<name>Update PEClass/ClubSport/InSeason - Eval</name>
		<sql>
			ALTER Procedure [dbo].[uproc_SelectInjuryEval]
			/ Param List /
			(
			@StudentID UniqueIdentifier,
			@InjuryID UniqueIdentifier
			)
			AS

			/******************************************************************************
			**        File:
			**        Name: uproc_SelectInjuryEval
			**        Desc:
			**
			**        This template can be customized:
			**
			**        Return values:
			**
			**        Called by:
			**
			**        Parameters:
			**        Input                            Output
			**     ----------                            -----------
			**
			**        Auth:
			**        Date:
			*******************************************************************************
			**        Change History
			*******************************************************************************
			**        Date:        Author:                Description:
			**        --------        --------                -------------------------------------------
			**
			*******************************************************************************/


			SELECT TOP 1
			[EvaluationID],
			[EvaluationDate],
			[ATCID],
			[Episode],
			[OnSetDate],
			[OnsetOther],
			[NatureOfOnset],
			[PriorHistory],
			[ActivityType_Practice],
			[ActivityType_Competition],
			[ActivityType_Conditioning],
			[ActivityType_WeightLifting],
			[ActivityType_Recreational],
			[ActivityType_NonAthletic],
			[ActivityType_OffSeason],
			[ActivityType_InSeason],
			[ActivityType_ClubSport] ,
			[ActivityType_PEClass] ,
			[Location],
			[SubComments],
			ms.[FName],
			ms.[LName],
			ms.[Age],
			ms.[Gender],
			ms.[Grade],
			si.[ParentEmailAddr],
			si.[SchoolList],
			[Sport],
			[LRB],
			[BodyArea],
			[BodyPart],
			[Site],
			[Classification],
			[Assessment],
			[ObjComments],
			[RecordDate],
			[RecordID],
			[InjuryID],
			si.[StudentID],
			[EvalComplete],
			OnsetOtherCheck
			FROM
			[dbo].[uInjuryEvaluation] si inner join dbo.mStudentInfo ms on si.StudentID = ms.StudentID
			WHERE
			si.StudentID = @StudentID AND
			si.InjuryID = @InjuryID
			ORDER BY
			RECORDID DESC
		</sql>
	   </script>
</dbscripts>
