WTS_ERAL_年假生成规则

--法定年假库存生成规则 
use WTS_ERAL
go
alter proc [dbo].proc_年假生成规则明细  
as    
begin    
 --创建临时表    
 create table #user_stock    
 (    
  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_stock    
 (    
  user_uid    
  ,user_code    
  ,user_name    
  ,leibie				--人员类别：1--合同工，2--劳务工，3--实习生
  ,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         --判断是否为一般用户，0 超级管理员，1 一般管理员，2 一般用户   
 --店铺人员没有年假    
 and isnull(user_def5,'')<>'2'   --员工类型  
 --实习人员没有年假    
 and isnull(leibie,'')<>'3'    

 --select * from #user_stock
 -----------------------    
 declare @begindate datetime    
 declare @enddate datetime    
    
 set @begindate=convert(varchar(5),getdate(),120)+'01-01'    --赋值 @begindate = '2020-01-01'
 set @enddate=convert(varchar(5),getdate(),120)+'12-31'    
 
 --三种情况时，入职日期当社会工龄起算日 1900-1-1 \ null \ ''
 update #user_stock set go_work_date=join_date where datediff(day,go_work_date,'1900-1-1')=0  or isnull(go_work_date,'')=''    
 update #user_stock set begindate=@begindate,enddate=@enddate,us_year=year(@begindate)    
    
 --工龄,司龄    
 update #user_stock 
 set 
	gongling=(datediff(day,go_work_date,begindate)+1)/365.00+tz_gongling/12, --嘉扬社会调整工龄的单位是月
	siling=(datediff(day,join_date,begindate)+1)/365.00  
--保证工龄和司龄不为负数
 update #user_stock set gongling=case when gongling<0 then 0 else gongling end,siling=case when siling<0 then 0 else siling end    
    
 --工龄折算日期1  当年入职的 = 当年的一月一日；之前年份入职的 = 入职日期  
 update #user_stock set zs_date1=begindate where xtype=1 and datediff(day,join_date,@begindate)>0 and gongling>0    --不是当年入职
 update #user_stock set zs_date1=join_date where xtype=1 and datediff(day,@begindate,join_date)>0 and gongling>0    --当年入职
    
 --工龄折算日期2    
 -------------------------------------------------    
 /*一、1月1日之前入职的*/    
 --1、工龄>=1    
 update #user_stock     
 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    
    
 --1、工龄>=1    
 update #user_stock     
 set zs_date2=zs_date1    
 where xtype=1     
 and gongling>=1    
 and floor(gongling) not in (9,19)    
 and siling>0    
 --2、工龄>0 and 工龄<1    
 update #user_stock     
 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    
 -------------------------------------------------    
 /*二、1月1日之后入职的*/    
 --1、工龄>=1 且工龄在9，19年    
 update #user_stock     
 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)    
 --2、工龄>=1 且工龄不在9，19年    
 update #user_stock     
 set zs_date2=zs_date1    
 where xtype=1     
 and gongling>=1    
 and siling=0    
 and floor(gongling) not in (9,19)    
 --2、工龄>0 and 工龄<1    
 update #user_stock     
 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    
    
 --法定年假计算    
 --工龄>=0年,工龄为9，19年的人    
 update #user_stock     
 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)    
    
 --工龄>0年且不跨9，19年    
 update #user_stock     
 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 #user_stock set amount=round(amount,0) where xtype=1    
 --二、0.5步长，如4.6为4.5，4.5为4.5，4.3为4.0    
 --update #user_stock set amount=case when floor(amount)+0.5<=amount then floor(amount)+0.5 else floor(amount) end where xtype=1    
 --三、向下取整    
 --update #user_stock set amount=floor(amount) where xtype=1    
    
 --年假天数转换成小时数，天数*工作系数    
 --update #user_stock set amount=amount*8.00    
     
 --开始有效日期，当年入职的取入职日期，上年入职的取入职日期加365天，否则为当年第一天    
 update #user_stock     
 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 #user_stock a,pub_user b    
  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 #user_stock    
end 

	---------------2020年5月14日23点52分 编辑