USE [Password]
GO
/****** Object:  StoredProcedure [dbo].[sp_Pass_SaveUser]    Script Date: 11/06/2013 18:34:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Pass_SaveUser] 
@EmployeeID varchar(10),
@LastName varchar(15),
@FirstName varchar(20),
@MiddleName varchar(15),
@Initial varchar(3),
@BirthDate varchar(12),
@Password varchar(32),
@DepartmentID varchar(4),
@OldDepartmentID varchar(4),
@Position varchar(25),
@RightCode1 varchar(1),
@RightCode2 varchar(1)

AS

DECLARE @Name varchar(50) /* for backcompatibility */
declare @cBirth varchar(5)

SET @Name = @LastName + ' ' + @FirstName + ' ' + @MiddleName

IF NOT EXISTS( SELECT EmployeeID FROM tbPasswordMain WHERE EmployeeID = @EmployeeID AND 
	DepartmentID = @DepartmentID)
	INSERT INTO tbPasswordMain
  	(EmployeeID, LastName, FirstName, MiddleName, Initial, BirthDate, Password, DepartmentID,
   	OriginalDepartment, Position, Right_Code1, Right_Code2, ODeptArt, SiteCode)
	VALUES
  	(@EmployeeID, @LastName, @FirstName, @MiddleName, @Initial, @BirthDate, @Password, 
   	@DepartmentID, @OldDepartmentID, @Position, @RightCode1, @RightCode2, '', '')
ELSE
	UPDATE tbPasswordMain
	SET LastName = @LastName,
	FirstName = @FirstName,
	MiddleName = @MiddleName, 
	Initial = @Initial,
	BirthDate = @BirthDate,
	[Password] =  @Password,
	DepartmentID = @DepartmentID,
   	OriginalDepartment = @OldDepartmentID,
	Position =  @Position,
	Right_Code1 =  @RightCode1,
	Right_Code2 = @RightCode2,
	ODeptArt = '', SiteCode = ''
	WHERE EmployeeID = @EmployeeID AND 
	DepartmentID = @DepartmentID
	

/* save to master file -- forward compatability */
if isdate(@BirthDate) = 1
  begin
    if cast(@BirthDate as datetime) <= '01/01/1900'
      begin
        set @cBirth = '00/00'
      end
    else
      begin
        set @cBirth = left(@BirthDate, 5)
      end
  end
else
  begin
    set @cBirth = '00/00'
  end

IF NOT EXISTS( SELECT EmployeeID FROM tbPasswordMaster WHERE EmployeeID = @EmployeeID)
begin
	insert into tbPasswordMaster
  	(EmployeeID, LastName, FirstName, MiddleName, Initial, BirthDate, Password)
	values(@EmployeeID, @LastName, @FirstName, @MiddleName, @Initial, @cBirth, @Password)
end
else
BEGIN
	update tbPasswordMaster
	set lastname= @LastName,
	firstname= @FirstName, 
	middlename= @MiddleName, 
	initial = @Initial, 
	birthdate = @cBirth, 
	password = @Password
	where employeeid = @employeeid
END	
/* 
   update all employee department password
   for consistency
 */
UPDATE tbPasswordMain SET Password = @Password
WHERE EmployeeID = @EmployeeID;

UPDATE tbPasswordMaster SET Password = @Password
WHERE EmployeeID = @EmployeeID;