use WTS_ERAL
go
alter proc [dbo].proc_年假生成规则明细
as
begin
create table
(
user_uid varchar(36),
user_code varchar(200),
user_name varchar(200),
leibie varchar(100),
go_work_date datetime,
join_date datetime,
tz_gongling decimal(18,2),
begindate datetime,
enddate datetime,
gongling decimal(18,2),
siling decimal(18,2),
xtype int,
zs_date1 datetime,
zs_date2 datetime,
amount decimal(18,2),
valid_begin datetime,
us_year int,
us_src int
)
insert into
(
user_uid
,user_code
,user_name
,leibie
,go_work_date
,join_date
,tz_gongling
,xtype
,us_src
)
select
user_uid
,user_code
,user_name
,leibie
,isnull(go_work_date,join_date)
,join_date
,case when ISNUMERIC(user_def6)>0 then cast(user_def6 as decimal(18,2))/12.00 else 0 end
,1
,9999
from pub_user
where user_status not in (4,5,6)
and user_type>1
and isnull(user_def5,'')<>'2'
and isnull(leibie,'')<>'3'
declare @begindate datetime
declare @enddate datetime
set @begindate=convert(varchar(5),getdate(),120)+'01-01'
set @enddate=convert(varchar(5),getdate(),120)+'12-31'
update
update
update
set
gongling=(datediff(day,go_work_date,begindate)+1)/365.00+tz_gongling/12,
siling=(datediff(day,join_date,begindate)+1)/365.00
update
update
update
update
set zs_date2=dbo.fn_getLeapYearDays(begindate,go_work_date)
where xtype=1
and gongling>=1
and floor(gongling) in (9,19)
and siling>0
update
set zs_date2=zs_date1
where xtype=1
and gongling>=1
and floor(gongling) not in (9,19)
and siling>0
update
set zs_date2=case when month(go_work_date)>=month(join_date) then dbo.fn_getLeapYearDays(begindate,go_work_date)
else dbo.fn_getLeapYearDays(begindate,join_date)
end
where xtype=1
and gongling>0 and gongling<1
and siling>0
update
set zs_date2=case when month(go_work_date)>=month(join_date) then dbo.fn_getLeapYearDays(begindate,go_work_date)
else dbo.fn_getLeapYearDays(begindate,join_date)
end
where xtype=1
and gongling>=1
and siling=0
and floor(gongling) in (9,19)
update
set zs_date2=zs_date1
where xtype=1
and gongling>=1
and siling=0
and floor(gongling) not in (9,19)
update
set zs_date2=case when month(go_work_date)>=month(join_date) then dbo.fn_getLeapYearDays(begindate,go_work_date)
else dbo.fn_getLeapYearDays(begindate,join_date)
end
where xtype=1
and gongling>0 and gongling<1
and siling=0
update
set amount=case when floor(gongling)=19 then 10.00*(datediff(day,zs_date1,zs_date2)+1)/365.00+15.00*(datediff(day,zs_date2,enddate)+1)/365.00
when floor(gongling)=9 then 5.00*(datediff(day,zs_date1,zs_date2)+1)/365.00+10.00*(datediff(day,zs_date2,enddate)+1)/365.00
end
where xtype=1
and gongling>=1
and floor(gongling) in (9,19)
update
set amount=case when gongling>=20 then 15.00
when gongling>=10 then 10.00*(datediff(day,zs_date1,zs_date2)+1)/365.00+10.00*(datediff(day,zs_date2,enddate)+1)/365.00
when gongling>=1 then 5.00*(datediff(day,zs_date1,zs_date2)+1)/365.00+5.00*(datediff(day,zs_date2,enddate)+1)/365.00
else 5.00*(datediff(day,zs_date2,enddate)+1)/365.00
end
where xtype=1
and gongling>0
and floor(gongling) not in (9,19)
update
update
set valid_begin=case when siling>0 and siling <1 then dateadd(day,365,join_date)
when siling=0 then join_date
else @begindate end
where isnull(amount,0)>0
INSERT INTO stock_data
(
stck_uid
,stck_order
,stck_comp_uid
,stck_user_uid
,stck_type_name
,stck_kc_category
,stck_type
,stck_data_type
,stck_year
,stck_period
,stck_period_begin
,stck_period_end
,stck_valid_begin
,stck_valid_end
,stck_amount
,stck_use
,stck_freeze
,stck_clear
,stck_unit
,stck_status
,stck_batch_uid
,stck_createtime
,stck_creator
,stck_src
)
SELECT
Newid()
,CONVERT(VARCHAR, @begindate, 112)+ Replace(CONVERT(VARCHAR, @begindate, 108), ':', '')+ '000'
,comp_id
,a.user_uid
,'法定年休假'
,'qj01'
,0
,100
,Year(@begindate)
,Year(@begindate)
,Dateadd(yy, Datediff(yy, 0, @begindate), 0)
,Dateadd(day, -1, Dateadd(yy, Datediff(yy, 0, @begindate) + 1, 0))
,a.valid_begin
,Cast(Cast(Year(@begindate)+1 AS VARCHAR) + '-'+ '03-31' AS DATETIME)
,amount * case when isnull(user_def3,'')='' then 0 else convert (numeric(18,2) ,user_def3) end
,0
,0
,0
,'hour'
,1
,'系统自动生成法定年假'
,getdate()
,'admin'
,0
from
where isnull(amount,0)>0
and a.user_uid=b.user_uid
and a.xtype=1
and convert(nvarchar(100),valid_begin,23)<=convert(nvarchar(100),getdate(),23)
and siling>0
and not exists
(
select 1 from stock_data sd
where sd.stck_type=0
and sd.stck_data_type=100
and sd.stck_user_uid=a.user_uid
and sd.stck_kc_category='qj01'
and sd.stck_year=a.us_year
and stck_src in(30,10,0)
)
update stock_data
set
stck_stcok_uid=stck_uid
,stck_correct_uid=stck_uid
where stck_data_type=100
and stck_kc_category='qj01'
and stck_stcok_uid is null
and stck_batch_uid ='系统自动生成法定年假'
drop table
end
---------------2020年5月14日23点52分 编辑