ALTER PROCEDURE [dbo].[GenerateMedPlafMass]
AS


DECLARE @year VARCHAR(12) = dbo.fn_formatdatetime(GETDATE(), 'yyyy')

DECLARE @issimulate VARCHAR (2)='0'
DECLARE @treattype varchar(50)=''
DECLARE @CreateBy VARCHAR(200)=''
DECLARE @ChangeBy VARCHAR(200)=''
DECLARE @startdate VARCHAR(20)=@year+'0101'
DECLARE @enddate VARCHAR(20)=@year+'1231'
DECLARE @paygradestart VARCHAR(20)=''
DECLARE @paygradeend VARCHAR(20)=''
DECLARE @empstart VARCHAR(200)='00000000'
DECLARE @empend VARCHAR(200)='99999999'
DECLARE @emptype VARCHAR(200)=''
DECLARE @empsubtype VARCHAR(200)=''


DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')
DECLARE @datecreate VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmddhhmmdd')
DECLARE @CreateDate VARCHAR(200)=''
DECLARE @ChangeDate VARCHAR(200)=''
set @CreateDate =@datecreate
set @ChangeDate =@datecreate


DECLARE @datatable as table (
HeaderSequence varchar(500),
Sequence varchar(500),
StartDate varchar(500),
EndDate varchar(500),
EmployeeID varchar(500),
TreatmentType varchar(500),
Plafond varchar(500),
PlafondRemain varchar(500),
PlafondUsed varchar(500),
MedicalFamily varchar(500),
PayrollGrade varchar(500),
CreateBy varchar(500),
CreateDate varchar(500),
ChangeBy varchar(500),
ChangeDate varchar(500)
)
DECLARE @datatablefinish as table (
HeaderSequence varchar(500),
Sequence varchar(500),
StartDate varchar(500),
EndDate varchar(500),
EmployeeID varchar(500),
TreatmentType varchar(500),
Plafond varchar(500),
PlafondRemain varchar(500),
PlafondUsed varchar(500),
MedicalFamily varchar(500),
PayrollGrade varchar(500),
CreateBy varchar(500),
CreateDate varchar(500),
ChangeBy varchar(500),
ChangeDate varchar(500)
)


if @emptype !='' or @emptype is not null
begin
select convert(varchar(50),'1')as HeaderSequence
,ROW_NUMBER()  OVER (partition by pa2.EmployeeID order by pa2.EmployeeID) as [Sequence],@startdate as StartDate,@enddate as EndDate,pa2.EmployeeID
,PLAF.TreatmentType,PLAF.FixedValue as Plafond,PLAF.FixedValue as PlafondRemain,convert(varchar(50),'0.00')as PlafondUsed ,PLAF.MedicalFamily
,pa3.PayrollGrade
into #temp
from PHRPA0002 pa2 left outer join 
PHRPA0003 pa3 on pa2.EmployeeID = pa3.EmployeeID and pa3.EndDate='99991231' left outer join 
PHRBNMEDTREATPLAF PLAF on PLAF.CompanyID = pa2.CompanyID and PLAF.PayClass = pa3.PayrollClass and PLAF.EndDate='99991231'
and (PLAF.PayClass = pa3.PayrollClass  or PLAF.PayClass ='*')and (PLAF.PayType = pa3.PayrollType  or PLAF.PayType ='*')
and (PLAF.PayGrade = pa3.PayrollGrade  or PLAF.PayGrade ='*')and (PLAF.PayArea = pa3.PayrollArea  or PLAF.PayArea ='*')
--and PLAF.TreatmentType = @treattype
where pa2.EmployeeStatus='01' and pa2.StartDate <= @now and pa2.EndDate >= @now and pa2.EmployeeType = @emptype 

update #temp set
HeaderSequence = a.HeaderSequence from (select  max(HeaderSequence) + 1as HeaderSequence,EmployeeID from phrpa0040 group by EmployeeID)a where a.EmployeeID = #temp.EmployeeID


insert into @datatable
select *,
 @CreateBy,
 @CreateDate,
 @ChangeBy,
 @ChangeDate
 from #temp

 drop table #temp
end

if @emptype ='' or @emptype is null
begin
select convert(varchar(50),'1')as HeaderSequence
,ROW_NUMBER()  OVER (partition by pa2.EmployeeID order by pa2.EmployeeID) as [Sequence],@startdate as StartDate,@enddate as EndDate,pa2.EmployeeID
,PLAF.TreatmentType,PLAF.FixedValue as Plafond,PLAF.FixedValue as PlafondRemain,convert(varchar(50),'0.00')as PlafondUsed ,PLAF.MedicalFamily
,pa3.PayrollGrade
into #temp2
from PHRPA0002 pa2 left outer join 
PHRPA0003 pa3 on pa2.EmployeeID = pa3.EmployeeID and pa3.EndDate='99991231' left outer join 
PHRBNMEDTREATPLAF PLAF on PLAF.CompanyID = pa2.CompanyID  and PLAF.EndDate='99991231'
and (PLAF.PayClass = pa3.PayrollClass  or PLAF.PayClass ='*')and (PLAF.PayType = pa3.PayrollType  or PLAF.PayType ='*')
and (PLAF.PayGrade = pa3.PayrollGrade  or PLAF.PayGrade ='*')and (PLAF.PayArea = pa3.PayrollArea  or PLAF.PayArea ='*')
--and PLAF.TreatmentType = @treattype
where pa2.EmployeeStatus='01' and pa2.StartDate <= @now and pa2.EndDate >= @now 
update #temp2 set
HeaderSequence = a.HeaderSequence from (select  max(HeaderSequence) + 1as HeaderSequence,EmployeeID from phrpa0040 group by EmployeeID)a where a.EmployeeID = #temp2.EmployeeID


insert into @datatable
select *,
 @CreateBy,
 @CreateDate,
 @ChangeBy,
 @ChangeDate
 from #temp2

drop table #temp2
end

if @paygradestart !='' and @empstart=''
begin
insert into @datatablefinish
select * from @datatable where PayrollGrade between @paygradestart and @paygradeend
end
if @empstart !='' and @paygradestart =''
begin
insert into @datatablefinish
select * from @datatable where  EmployeeID between @empstart and @empend 
end
if @paygradestart !='' and @empstart !=''
begin
insert into @datatablefinish
select * from @datatable where PayrollGrade between @paygradestart and @paygradeend and EmployeeID between @empstart and @empend 
end



if @issimulate !='0'
begin
insert into PHRPA0040
select distinct
StartDate ,
EndDate ,
EmployeeID ,
HeaderSequence ,
'',
'System Mass',
CreateBy ,
CreateDate ,
ChangeBy ,
ChangeDate ,
'','','','','','','','','','','','','','',0,'','','',null
from @datatablefinish

insert into PHRPA0041
select 
EmployeeID ,
HeaderSequence ,
Sequence,
TreatmentType,
Plafond,
PlafondRemain,
PlafondUsed,
MedicalFamily,
''
from @datatablefinish
end
--select distinct EmployeeID,'Sukses Generate'as Status from @datatablefinish

select 
EmployeeID ,
HeaderSequence ,
Sequence,
TreatmentType,
Plafond,
PlafondRemain,
PlafondUsed,
MedicalFamily,
CASE WHEN @issimulate='0' then 'Simulate' WHEN @issimulate='1' then 'Save Success' end as Status
from @datatablefinish