sqlsever 1

USE [T120180806092430345]
GO
/****** Object: StoredProcedure [dbo].[NJ] Script Date: 11/06/2018 14:07:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[NJ]
@pid uniqueidentifier, --年度期间ID
@bdate date , --周期开始日期
@edate date , --周期结束日期
@sdate date , --计算日期
@deptid uniqueidentifier --部门ID

as
WITH T AS (SELECT ID, ParentId FROM Tecul_Department WHERE ID = @deptid
UNION ALL SELECT A.ID, A.ParentId FROM Tecul_Department AS A JOIN T AS B ON A.ParentId = B.ID--这里获取上面输入Id的所有子类
) SELECT Id into #tables FROM T ORDER BY ID;

declare @years int --工作年限
declare @VacationHours decimal --假期总时长
declare @YXTime decimal --已休时间
declare @LeaveHour decimal --请假单时间
declare @ADVacHours decimal --调整总时长
declare @ADYxtime decimal --调整已休时长
declare @AskLeaveH decimal --请假时长

DECLARE MyCursor CURSOR
FOR
select K.Id,/*职员ID*/ K.HireDate, /*入职时间*/ K.TakePart, K.Status, K.DeptId, K.ItemName, K.ResignationDate from Tecul_Employee K inner join #tables on K.DeptId = #tables.Id where IsDelete != 1 and ((HireDate < @edate and Status='在职') or (Status='离职' and ResignationDate > @bdate));

open MyCursor
--print 11
--drop table #tables
--循环一个游标.
DECLARE @ID uniqueidentifier,@HireDate date,@TakePart date,@EmpStatus nvarchar(50),@EmpDeptid uniqueidentifier,@EmpName nvarchar(50),@ResignationDate date
FETCH NEXT FROM MyCursor INTO @ID,@HireDate,@TakePart,@EmpStatus,@EmpDeptid,@EmpName,@ResignationDate
WHILE @@FETCH_STATUS =0
begin
if exists(select * from Tecul_AttnLeaveType where WorkingYears is not null)
begin
print '有假期类型'+@EmpName
declare AttnLeaveType cursor
for select M.Id,M.RoundingMode, --取数方式
M.WorkingYears, --在司工龄 --社会工龄 "a43303c4-b9fb-413b-9119-486941a81348") "30d49f67-07b1-4d90-9b34-155fcd10db66")
M.TimeExpand
from Tecul_AttnLeaveType M where WorkingYears is not null
open AttnLeaveType
declare @leavetypeid uniqueidentifier,@RoundingMode uniqueidentifier,@WorkingYears uniqueidentifier,@TimeExpand decimal
fetch next from AttnLeaveType into @leavetypeid ,@RoundingMode ,@WorkingYears ,@TimeExpand
WHILE @@FETCH_STATUS =0
begin
set @years = 0
set @VacationHours = 0
set @YXTime = 0
set @LeaveHour = 0
set @ADVacHours = 0
set @ADYxtime = 0
set @AskLeaveH = 0
if (@WorkingYears = 'a43303c4-b9fb-413b-9119-486941a81348') --在司工龄
begin
if(@RoundingMode = 'c2413f15-7451-4305-b64f-e77a567da510') --向上取整
begin
set @years = CEILING((DATEDIFF(DAY, @HireDate,@sdate))/365)
end
else if(@RoundingMode = '399b437d-d92b-493e-994d-cde272dc7162"') --向下取整
begin
set @years = FLOOR((DATEDIFF(DAY, @HireDate,@sdate))/365)
end
else if(@RoundingMode = '722938fd-2fda-4a71-8109-55781bd82384"') --四舍五入
begin
set @years = round((DATEDIFF(DAY, @HireDate,@sdate))/365,0)
end
end
else if (@WorkingYears = '30d49f67-07b1-4d90-9b34-155fcd10db66') --社会工龄
begin
if(@RoundingMode = 'c2413f15-7451-4305-b64f-e77a567da510') --向上取整
begin
set @years = CEILING((DATEDIFF(DAY, @TakePart,@sdate))/365)
end
else if(@RoundingMode = '399b437d-d92b-493e-994d-cde272dc7162"') --向下取整
begin
set @years = FLOOR((DATEDIFF(DAY, @TakePart,@sdate))/365)
end
else if(@RoundingMode = '722938fd-2fda-4a71-8109-55781bd82384"') --四舍五入
begin
set @years = round((DATEDIFF(DAY, @TakePart,@sdate))/365,0)
end
end
update Tecul_AttnLeaveTypeEntity set @VacationHours = VacationHours where Id = @leavetypeid and IsDelete =0 and IsHistoryFlag=0 and @years >= YearBegin and @years <YearEnd

if exists(select * from Tecul_AnnualAdjust where Period= @pid and EmpCode=@ID and LeaveType=@leavetypeid)
begin
update Tecul_AnnualAdjust set @ADVacHours = Amount where Period= @pid and EmpCode=@ID and LeaveType=@leavetypeid
update Tecul_AnnualAdjust set @ADYxtime = Yx where Period= @pid and EmpCode=@ID and LeaveType=@leavetypeid
end
--set @years = @years+ CAST(@ADVacHours as int)

if exists(select * from Tecul_AttnLeave where EmpCode =@ID and LeaveType=@leavetypeid and WorkflowStatus='已审核' and IsDelete=0 and IsHistoryFlag=0)
begin
declare attnLeaves cursor for select AL.LeaveHour,AL.Id from Tecul_AttnLeave AL where AL.EmpCode =@ID and AL.LeaveType=@leavetypeid and Al.WorkflowStatus='已审核' and AL.IsDelete=0 and AL.IsHistoryFlag=0
OPEN attnLeaves
declare @leavetime decimal,@attnLeaveid uniqueidentifier
fetch next from attnLeaves into @leavetime,@attnLeaveid
while @@FETCH_STATUS = 0
begin
print '存在请假！'
--set @LeaveHour = @LeaveHour+@leavetime
select @LeaveHour = sum(TakeHour) from (select * from Tecul_AttnLeaveEntity where Id=@attnLeaveid and Period= @pid and IsDelete=0 and IsHistoryFlag=0) Tecul_AttnLeaveEntity
fetch next from attnLeaves into @leavetime,@attnLeaveid
end
close attnLeaves
deallocate attnLeaves
end
print @LeaveHour
if exists(select * from Tecul_AnnualLeaveRecord where EmpCode=@ID and Period=@pid and LeaveType=@leavetypeid and IsDelete=0 and IsHistoryFlag=0)
begin
--update
update Tecul_AnnualLeaveRecord set EmpName=@EmpName,Status=@EmpStatus,DeptId=@EmpDeptid,Hiredate=@HireDate,Amount=(@VacationHours+@ADVacHours),Quitdate=@ResignationDate,BeginDate=@bdate,EndDate=DATEADD(DAY,@TimeExpand,@edate),LastUpdater=@ID,LastUpdateDate=GETDATE(),Yx=(@YXTime+@ADYxtime+@LeaveHour),Wx=(@VacationHours+@ADVacHours-@YXTime-@ADYxtime-@LeaveHour),Yars=@years where EmpCode=@ID and Period=@pid and LeaveType=@leavetypeid and IsDelete=0 and IsHistoryFlag=0
end
else
begin
--add
if (@VacationHours + @ADVacHours ) >0
begin
insert into Tecul_AnnualLeaveRecord (Id,Period,Hiredate,Amount,BeginDate,EndDate,Status,EmpCode,EmpName,DeptId,LeaveType,Quitdate,Creater,CreateDate,LastUpdater,LastUpdateDate,ItemCode,ItemName,IsDelete,IsHistoryFlag,Remark,WorkflowStatus,CreateType,LockType,Yx,Wx,Yars) values (NEWID(),@pid,@HireDate,(@VacationHours+@ADVacHours),@bdate,DATEADD(DAY,@TimeExpand,@edate),@EmpStatus,@ID,@EmpName,@EmpDeptid,@leavetypeid,@ResignationDate,@ID, GETDATE(),@ID,GETDATE(),'','',0,0,'','',0,0,(@YXTime+@ADYxtime+@LeaveHour),(@VacationHours+@ADVacHours-@LeaveHour-@YXTime-@ADYxtime),@years)
end
end
fetch next from AttnLeaveType into @leavetypeid ,@RoundingMode ,@WorkingYears ,@TimeExpand
end
close AttnLeaveType
deallocate AttnLeaveType
end
FETCH NEXT FROM MyCursor INTO @ID,@HireDate,@TakePart,@EmpStatus,@EmpDeptid,@EmpName,@ResignationDate
END

close MyCursor

DEALLOCATE MyCursor