USE [fau]
GO
/****** 对象: StoredProcedure [dbo].[sp_Check_Emp_Holiday] 脚本日期: 06/09/2010 13:34:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Check_Emp_Holiday]
as
declare @E_ID int,
@E_TYPE nvarchar(50),
@E_HIRE_DATE datetime,
@E_SOCIAL_WORK_DATE datetime
declare @Holiday numeric(4, 2),
@Days int
--定义一个游标并打开它
begin
declare mycursor cursor for select E_ID,E_TYPE,E_SOCIAL_WORK_DATE,E_HIRE_DATE from dbo.EMPLOYEE_INFO
open mycursor
--开始提取记录,放入指定的变量
fetch next from mycursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
while @@fetch_status = 0
begin
set @E_SOCIAL_WORK_DATE = DATEDIFF(day,@E_SOCIAL_WORK_DATE,getdate())
set @Holiday=0
/*员工类型为Loacl*/
if (@E_TYPE=N'Local')
begin
/*1.员工加入佛吉亚工作未满一年时,年假的计算按月份比例计算(每月为1.25天), 15日前入职(含15日)不计算当月年假,15日后入职员工当月年假计算为1.25天。*/
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=1.25*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=1.25*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
/* 员工加入佛吉亚后的第二年至第五年*/
/*员工加入佛吉亚后的第十一年至第二十年*/
--else if (CONVERT(int,@E_HIRE_DATE,0)/365.0>10 and CONVERT(int,@E_HIRE_DATE,0)/365.0<=20)
else if (dbo.ISOyear(@E_HIRE_DATE)>1 and dbo.ISOyear(@E_HIRE_DATE)<=5)
set @Holiday=15
/* 员工加入佛吉亚后的第六年至第十年*/
else if (dbo.ISOyear(@E_HIRE_DATE)>5 and dbo.ISOyear(@E_HIRE_DATE)<=10)
set @Holiday=17
/*员工加入佛吉亚后的第十一年至第二十年*/
else if (dbo.ISOyear(@E_HIRE_DATE)>10 and dbo.ISOyear(@E_HIRE_DATE)<=20)
set @Holiday=20
else
set @Holiday=20
end
/*当员工类型为Expats*/
else if (@E_TYPE=N'Expats')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=24
end
end
/*当员工类型为Secondee*/
else if (@E_TYPE='Secondee')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=24
end
end
/*当员工类型为VIE*/
else if (@E_TYPE=N'VIE')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=30.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=30.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=30
end
end
/*当员工类型为spcial*/
else if (@E_TYPE=N'special')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=20.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=20.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=20
end
end
/*当员工类型为spcial2*/
else if (@E_TYPE=N'special2')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=24.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=24.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=24
end
end
/*当员工类型为spcial3*/
else if (@E_TYPE=N'special3')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=26.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=26.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=26
end
end
--update dbo.EMPLOYEE_HOLIDAY set H_CREATE_NUM=@Holiday where H_STATUS='Y' and E_ID=@E_ID
exec dbo.SP_MODIFY_HOLIDAY @E_ID,@Holiday
fetch next from mycursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
end
--关闭游标,释放内存
close mycursor
deallocate mycursor
end
USE [fau]
GO
/****** 对象: StoredProcedure [dbo].[SP_EMP_HOLIDAY] 脚本日期: 06/09/2010 13:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_EMP_HOLIDAY]
as
declare @E_ID int,
@E_TYPE nvarchar(50),
@E_HIRE_DATE datetime,
@E_SOCIAL_WORK_DATE datetime
declare @Holiday numeric(4, 2),
@Days int
--定义一个游标并打开它
declare tablecursor cursor for select E_ID,E_TYPE,E_SOCIAL_WORK_DATE,E_HIRE_DATE from dbo.EMPLOYEE_INFO
begin
open tablecursor
--开始提取记录,放入指定的变量
fetch next from tablecursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
while @@fetch_status = 0
begin
update dbo.EMPLOYEE_HOLIDAY
set H_STATUS='N'
where E_ID=@E_ID
set @E_SOCIAL_WORK_DATE = DATEDIFF(day,@E_SOCIAL_WORK_DATE,getdate())
set @Holiday=0
/*员工类型为Loacl*/
if (@E_TYPE=N'Local')
begin
/*1.员工加入佛吉亚工作未满一年时,年假的计算按月份比例计算(每月为1.25天), 15日前入职(含15日)不计算当月年假,15日后入职员工当月年假计算为1.25天。*/
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=1.25*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=1.25*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
/* 员工加入佛吉亚后的第二年至第五年*/
else if (dbo.ISOyear(@E_HIRE_DATE)>1 and dbo.ISOyear(@E_HIRE_DATE)<=5)
set @Holiday=15
/* 员工加入佛吉亚后的第六年至第十年*/
else if (dbo.ISOyear(@E_HIRE_DATE)>5 and dbo.ISOyear(@E_HIRE_DATE)<=10)
set @Holiday=17
/*员工加入佛吉亚后的第十一年至第二十年*/
else if (dbo.ISOyear(@E_HIRE_DATE)>10 and dbo.ISOyear(@E_HIRE_DATE)<=20)
set @Holiday=20
else
set @Holiday=20
end
/*当员工类型为Expats*/
else if (@E_TYPE=N'Expats')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=24
end
end
/*当员工类型为Secondee*/
else if (@E_TYPE='Secondee')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=24/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=24/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=24
end
end
/*当员工类型为VIE*/
else if (@E_TYPE=N'VIE')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=30.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=30.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=30
end
end
/*当员工类型为spcial*/
else if (@E_TYPE=N'special')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=20.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=20.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=20
end
end
/*当员工类型为spcial2*/
else if (@E_TYPE=N'special2')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=24.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=24.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=24
end
end
/*当员工类型为spcial3*/
else if (@E_TYPE=N'special3')
begin
if(DATEDIFF(month,@E_HIRE_DATE,getdate())<=12)
begin
set @Days=DATEPART(day, @E_HIRE_DATE)
if (@Days<=15)
set @Holiday=26.00/12*DATEDIFF(month,@E_HIRE_DATE,getdate())
else
set @Holiday=26.00/12*(DATEDIFF(month,@E_HIRE_DATE,getdate())-1)
end
else
begin
set @Holiday=26
end
end
-- print(@Holiday)
insert into dbo.EMPLOYEE_HOLIDAY (E_ID,EH_YEAR,H_CREATE_NUM,H_CREATE_DATE,H_STATUS) values
(@E_ID,year(getdate()),@Holiday,getdate(),N'Y')
fetch next from tablecursor into @E_ID,@E_TYPE,@E_SOCIAL_WORK_DATE,@E_HIRE_DATE
end
--关闭游标,释放内存
close tablecursor
deallocate tablecursor
end
USE [fau]
GO
/****** 对象: StoredProcedure [dbo].[SP_MODIFY_HOLIDAY] 脚本日期: 06/09/2010 13:35:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_MODIFY_HOLIDAY]
(
@E_ID int,
@Holiday numeric(4, 2)
)
AS
declare @H_CREATE_NUM numeric(4, 2)
declare mycursor1 cursor for select H_CREATE_NUM from dbo.EMPLOYEE_HOLIDAY where E_ID=@E_ID and H_STATUS='Y'
BEGIN
open mycursor1
--开始提取记录,放入指定的变量
fetch next from mycursor1 into @H_CREATE_NUM
while @@fetch_status = 0
begin
if(@H_CREATE_NUM!=@Holiday)
begin
/*print @E_ID
print @H_CREATE_NUM;
print @Holiday;*/
update dbo.EMPLOYEE_HOLIDAY set H_STATUS='N' where E_ID=@E_ID and H_STATUS='Y'
insert into dbo.EMPLOYEE_HOLIDAY (E_ID,EH_YEAR,H_CREATE_NUM,H_CREATE_DATE,H_STATUS) values
(@E_ID,year(getdate()),@Holiday,getdate(),N'Y')
end
break;
end
--关闭游标,释放内存
close mycursor1
deallocate mycursor1
END
USE [fau]
GO
/****** 对象: UserDefinedFunction [dbo].[ISOyear] 脚本日期: 06/09/2010 13:36:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ISOyear] (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ISOyear int;
if(Year(Getdate())>Year(@DATE))
set @ISOyear=Year(Getdate())-Year(@DATE)
if(Month(Getdate())=Month(@DATE))
begin
if(Day(Getdate())>=Day(@DATE))
set @ISOyear=@ISOyear+1
end
else if (Month(Getdate())>Month(@DATE))
set @ISOyear=@ISOyear+1
RETURN(@ISOyear);
END;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_alterdiagram]
(
@diagramname sysname,
@owner_id int = null,
@version int,
@definition varbinary(max)
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
set nocount on
declare @theId int
declare @retval int
declare @IsDbo int
declare @UIDFound int
declare @DiagId int
declare @ShouldChangeUID int
if(@diagramname is null)
begin
RAISERROR ('Invalid ARG', 16, 1)
return -1
end
execute as caller;
select @theId = DATABASE_PRINCIPAL_ID();
select @IsDbo = IS_MEMBER(N'db_owner');
if(@owner_id is null)
select @owner_id = @theId;
revert;
select @ShouldChangeUID = 0
select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
begin
RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
return -3
end
if(@IsDbo <> 0)
begin
if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
begin
select @ShouldChangeUID = 1 ;
end
end
-- update dds data
update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;
-- change owner
if(@ShouldChangeUID = 1)
update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;
-- update dds version
if(@version is not null)
update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;
return 0
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_creatediagram]
(
@diagramname sysname,
@owner_id int = null,
@version int,
@definition varbinary(max)
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
set nocount on
declare @theId int
declare @retval int
declare @IsDbo int
declare @userName sysname
if(@version is null or @diagramname is null)
begin
RAISERROR (N'E_INVALIDARG', 16, 1);
return -1
end
execute as caller;
select @theId = DATABASE_PRINCIPAL_ID();
select @IsDbo = IS_MEMBER(N'db_owner');
revert;
if @owner_id is null
begin
select @owner_id = @theId;
end
else
begin
if @theId <> @owner_id
begin
if @IsDbo = 0
begin
RAISERROR (N'E_INVALIDARG', 16, 1);
return -1
end
select @theId = @owner_id
end
end
-- next 2 line only for test, will be removed after define name unique
if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
begin
RAISERROR ('The name is already used.', 16, 1);
return -2
end
insert into dbo.sysdiagrams(name, principal_id , version, definition)
VALUES(@diagramname, @theId, @version, @definition) ;
select @retval = @@IDENTITY
return @retval
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_dropdiagram]
(
@diagramname sysname,
@owner_id int = null
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
set nocount on
declare @theId int
declare @IsDbo int
declare @UIDFound int
declare @DiagId int
if(@diagramname is null)
begin
RAISERROR ('Invalid value', 16, 1);
return -1
end
EXECUTE AS CALLER;
select @theId = DATABASE_PRINCIPAL_ID();
select @IsDbo = IS_MEMBER(N'db_owner');
if(@owner_id is null)
select @owner_id = @theId;
REVERT;
select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname
if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
begin
RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
return -3
end
delete from dbo.sysdiagrams where diagram_id = @DiagId;
return 0;
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_helpdiagrams]
(
@diagramname sysname = NULL,
@owner_id int = NULL
)
WITH EXECUTE AS N'dbo'
AS
BEGIN
DECLARE @user sysname
DECLARE @dboLogin bit
EXECUTE AS CALLER;
SET @user = USER_NAME();
SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
REVERT;
SELECT
[Database] = DB_NAME(),
[Name] = name,
[ID] = diagram_id,
[Owner] = USER_NAME(principal_id),
[OwnerID] = principal_id
FROM
sysdiagrams
WHERE
(@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
(@diagramname IS NULL OR name = @diagramname) AND
(@owner_id IS NULL OR principal_id = @owner_id)
ORDER BY
4, 5, 1
END