USE [EnVisage]
GO

/****** Object:  StoredProcedure [dbo].[sp_AddBulkPeopleResource]    Script Date: 08/28/2015 11:01:19 AM ******/
DROP PROCEDURE [dbo].[sp_AddBulkPeopleResource]
GO

/****** Object:  StoredProcedure [dbo].[sp_AddBulkPeopleResource]    Script Date: 08/28/2015 11:01:19 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[sp_AddBulkPeopleResource] (
@FirstName nvarchar(250),
@LastName  nvarchar(250),
@Title		nvarchar(200),
@CreditNumber nvarchar(100),
@StartDate datetime=null,
@EndDate	datetime=null,
@isActive  bit,
@EmployeeID nvarchar(100),
@ProcessID	nvarchar(100),
@RecordNumber	int

) AS
	BEGIN
	
		BEGIN TRANSACTION
		--	declare @EndDate datetime
		--using title and creditnumber determine if we have matching records to get an EC
			declare @ECID uniqueidentifier 
			declare @CreditDepertmentID uniqueidentifier
			declare @EXID uniqueidentifier
			-- get credit department id
			select @CreditDepertmentID=Id from CreditDepartment where CreditNumber=@CreditNumber
			--get expenditure id based on title
			select @EXID=Id from Expenditure where Name=@Title

			if @EXID is null
			begin
				insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO Expenditure for "'+@Title+'"  Record skipped',GETDATE())
				goto exit_commit
			end
			 if @CreditDepertmentID  is null
			 begin
				insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO CreditCode for "'+@CreditNumber+'"  Record skipped',GETDATE())
				goto exit_commit
			end
			--if we have a credit department id and expenditure id we can get 
			--excpediture category id used to tie to the people resourse
			select @ECID=Id from ExpenditureCategory where ExpenditureId=@EXID and CreditId=@CreditDepertmentID
			if @ECID is null
			begin
				insert into supt_ImportErrors (ProcessID,RecordNbr,Message,DateTimeProcessed) values (@ProcessID,@RecordNumber,'NO Expenditure category found for Expenditure:"'+@title+'" Credit Code:"'+@CreditNumber+'"  Record skipped',GETDATE())
				goto exit_commit
			end
			--see if we have an existing people resource record.
			--first search is by first name and employeeid, second is by first and last name
			declare @CurPeopleResourceRecID uniqueidentifier 
			select @CurPeopleResourceRecID= Id FROM PeopleResource where FirstName=@FirstName and EmployeeID=@EmployeeID
			if @CurPeopleResourceRecID is null
			begin
				select @CurPeopleResourceRecID= Id  FROM PeopleResource where FirstName=@FirstName and LastName=@LastName
			end 
			-- if we do not have an existing record create a new one
			if @CurPeopleResourceRecID is null
			begin
				if isnull(@EndDate,'')=''
					set @EndDate=DATEADD (YEAR , 50 , getdate() )
				if isnull(@StartDate,'')=''
					set @StartDate=getdate()

				--inserting a new record, the start date should be today, the end date will be today +50 years.
				insert into PeopleResource ( FirstName,LastName,IsActiveEmployee,ExpenditureCategoryId,StartDate,EndDate,EmployeeID) values (@FirstName,@LastName,@isActive,@ECID,@StartDate,@EndDate,@EmployeeID)
			end
			--we have an existing record determine if we have to set the start/end dates based on isActive value
			else
			begin
				declare @curECID uniqueidentifier
				declare @StartDateCur datetime  = GetDate();
				declare @EndDateCur datetime  = GetDate();
				declare @isActiveCur bit
				select @curECID=ExpenditureCategoryId,@isActiveCur=IsActiveEmployee,@StartDateCur=StartDate,@EndDateCur=EndDate FROM PeopleResource where FirstName=@FirstName and LastName=@LastName
				if @isActiveCur <> @isActive
				begin
					if @isActive = 0
					begin
						if isnull(@EndDate ,'') = ''
						set @EndDate=getdate();
					end
					else
					begin
						if @StartDate is null
							set @StartDate=DATEADD(DAY,-1,getdate());
						if isnull(@EndDate ,'') = ''
							set @EndDate=DATEADD (YEAR , 50 , getdate() );
					end
				end 
				else
				begin
					set @StartDate=@StartDateCur
					set @EndDate=@EndDateCur
				end 
				update PeopleResource set StartDate=@StartDate,EndDate=@EndDate, IsActiveEmployee=@isActive, ExpenditureCategoryId=@ECID ,LastName=@LastName, EmployeeID=@EmployeeID where ID=@CurPeopleResourceRecID
			  end 

	exit_commit:
		COMMIT TRANSACTION 
 	END



GO