-- 获取某公司(或项目)的一个时间段内的休息日天数(暂用在SP3_SystemJumpCheckTach及CTCheckContentView.GetCheckOption中)
ALTER FUNCTION [dbo].[F3_GetHolidays]
(
@WCCID VARCHAR(50), -- 公司或项目ID
@SD DATETIME, -- 开始日期
@ED DATETIME -- 结束日期
) RETURNS DECIMAL(18,4)
BEGIN
DECLARE @WCID VARCHAR(50)
DECLARE @Cnt DECIMAL(18,4)
DECLARE @IsWorkDay BIT
SELECT @WCID=WCID FROM TWorkCalendarConfig WHERE WCCID=@WCCID
SET @Cnt=0
IF (@SD<@ED)
BEGIN
SET @IsWorkDay=1
IF (DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
SET @IsWorkDay=0
SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0
-- 如果是同一天
IF (DATEDIFF(dd,@SD,@ED)=0)
BEGIN
IF(@IsWorkDay=0)
SET @Cnt=DATEDIFF(ss,@SD,@ED)/86400.00
END
ELSE
BEGIN
-- (1)计算开始日期当天的休息天数
IF(@IsWorkDay=0)
SET @Cnt=1-(DATEPART(hh,@SD)*3600+DATEPART(mi,@SD)*60+DATEPART(ss,@SD))/86400.00
-- (2)计算结束日期当天的休息天数
SET @IsWorkDay=1
IF (DATEPART(dw,@ED)=1 OR DATEPART(dw,@ED)=7)
SET @IsWorkDay=0
SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@ED)=0
IF(@IsWorkDay=0)
SET @Cnt=@Cnt+(DATEPART(hh,@ED)*3600+DATEPART(mi,@ED)*60+DATEPART(ss,@ED))/86400.00
-- (3)计算开始日期和结束日期之间的日期
SET @SD=DATEADD(dd,1,CAST(YEAR(@SD) AS VARCHAR(4))+'-'+CAST(MONTH(@SD) AS VARCHAR(2))+'-'+CAST(DAY(@SD) AS VARCHAR(2)))
SET @ED=DATEADD(dd,-1,CAST(YEAR(@ED) AS VARCHAR(4))+'-'+CAST(MONTH(@ED) AS VARCHAR(2))+'-'+CAST(DAY(@ED) AS VARCHAR(2)))
WHILE (@SD<=@ED)
BEGIN
SET @IsWorkDay=1
IF(DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
SET @IsWorkDay=0
SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0
IF(@IsWorkDay=0)
SET @Cnt=@Cnt+1
SET @SD=DATEADD(dd,1,@SD)
END
END
END
RETURN (@Cnt)
END --休息日天数,另一种计算方式用到
-- 获取某公司(或项目)的一个时间段内的工作日分钟(暂用在CTCheckContentView.GetCheckOption中)
ALTER FUNCTION [dbo].[F3_GetWorkMinutes]
(
@CorpID VARCHAR(50), -- 公司ID
@SD DATETIME, -- 开始日期
@ED DATETIME -- 结束日期
) RETURNS INT
BEGIN
DECLARE @WCID VARCHAR(50)
DECLARE @Cnt DECIMAL(18,4)
DECLARE @IsWorkDay BIT
DECLARE @OnTime DATETIME,@MorningOffTime DATETIME,@AfternoonOnTime DATETIME,@OffTime DATETIME
SELECT @WCID=WCID FROM TWorkCalendarConfig WHERE WCCID=@CorpID
SELECT @OnTime='09:00',@MorningOffTime='12:00',@AfternoonOnTime='13:30',@OffTime='17:30'
SELECT @OnTime=UpTime,@MorningOffTime=NoonDownTime,@AfternoonOnTime=DATEADD(mi,RestMinutes,NoonDownTime),@OffTime=DownTime
FROM IDOADB.dbo.TWorkTimeOption
WHERE CorpID=@CorpID
SET @Cnt=0
IF (@SD<@ED)
BEGIN
SET @IsWorkDay=1
IF (DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
SET @IsWorkDay=0
SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0
-- 如果是同一天
IF (DATEDIFF(dd,@SD,@ED)=0)
BEGIN
IF(@IsWorkDay=1)
SET @Cnt=dbo.F3_GetWorkMinutesByRange(@OnTime,@MorningOffTime,@AfternoonOnTime,@OffTime,@SD,@ED)
END
ELSE
BEGIN
-- (1)计算开始日期当天的工作分钟
IF(@IsWorkDay=1)
SET @Cnt=dbo.F3_GetWorkMinutesByRange(@OnTime,@MorningOffTime,@AfternoonOnTime,@OffTime,@SD,NULL)
-- (2)计算结束日期当天的工作分钟
SET @IsWorkDay=1
IF (DATEPART(dw,@ED)=1 OR DATEPART(dw,@ED)=7)
SET @IsWorkDay=0
SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@ED)=0
IF(@IsWorkDay=1)
SET @Cnt=@Cnt+dbo.F3_GetWorkMinutesByRange(@OnTime,@MorningOffTime,@AfternoonOnTime,@OffTime,NULL,@ED)
-- (3)计算开始日期和结束日期之间的工作分钟
SELECT @SD=DATEADD(dd,1,CONVERT(VARCHAR(10),@SD,120)),@ED=DATEADD(dd,-1,CONVERT(VARCHAR(10),@ED,120))
WHILE (@SD<=@ED)
BEGIN
SET @IsWorkDay=1
IF(DATEPART(dw,@SD)=1 OR DATEPART(dw,@SD)=7)
SET @IsWorkDay=0
SELECT @IsWorkDay=IsWorkDay FROM TWorkCalendarDetail
WHERE WCID=@WCID AND DATEDIFF(dd,[Date],@SD)=0
IF(@IsWorkDay=1)
SET @Cnt=@Cnt+DATEDIFF(mi,@OnTime,@MorningOffTime)+DATEDIFF(mi,@AfternoonOnTime,@OffTime)
SET @SD=DATEADD(dd,1,@SD)
END
END
END
RETURN (ROUND(@Cnt,0))
END
-- 获取工作日内某个时间段内的工作分钟(暂用在OperAllowDB.dbo.F3_GetWorkdays中)
-- 作者:程爱民
-- 日期:2012-11-28
ALTER FUNCTION [dbo].[F3_GetWorkMinutesByRange]
(
@OnTime DATETIME, -- 上班时间
@MorningOffTime DATETIME, -- 上午下班时间(午休开始时间)
@AfternoonOnTime DATETIME, -- 下午上班时间(午休结束时间)
@OffTime DATETIME, -- 下班时间
@SD DATETIME, -- 开始时间(当@ED为NULL时,获取以@SD为)
@ED DATETIME -- 结束时间
) RETURNS DECIMAL(18,4)
BEGIN
DECLARE @Cnt DECIMAL(18,4)
SET @Cnt=0.00
SELECT @SD=CONVERT(VARCHAR(8),@SD,108),@ED=CONVERT(VARCHAR(8),@ED,108)
SELECT @SD=ISNULL(@SD,@OnTime),@ED=ISNULL(@ED,@OffTime)
IF (@SD<@ED)
BEGIN
-- 上午工作分钟
IF (@SD<@MorningOffTime)
SET @Cnt=DATEDIFF(ss,
CASE WHEN @SD<@OnTime THEN @OnTime ELSE @SD END,
CASE WHEN @ED>@MorningOffTime THEN @MorningOffTime ELSE @ED END)/60.0
-- 下午工作分钟
IF (@ED>@AfternoonOnTime)
SET @Cnt=@Cnt+DATEDIFF(ss,
CASE WHEN @SD<@AfternoonOnTime THEN @AfternoonOnTime ELSE @SD END,
CASE WHEN @ED>@OffTime THEN @OffTime ELSE @ED END)/60.0
IF (@Cnt<0)
SET @Cnt=0
END
RETURN (@Cnt)
END