USE [MinovaES_HINO_Dev]
GO
/****** Object:  StoredProcedure [dbo].[GenerateMedPlafMass]    Script Date: 27/12/2023 11.28.38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GenerateMedPlafMass]
(
@issimulate VARCHAR(20),
@CreateBy VARCHAR(200),
@ChangeBy VARCHAR(200),
@startdate VARCHAR(20),
@enddate VARCHAR(20),
@paygradestart VARCHAR(20),
@paygradeend VARCHAR(20),
@empstart VARCHAR(200),
@empend VARCHAR(200),
@emptype VARCHAR(200),
@empsubtype VARCHAR(200)
)
AS



--DECLARE @issimulate VARCHAR (2)='0'
--DECLARE @treattype varchar(50)=''
--DECLARE @CreateBy VARCHAR(200)='00000111'
--DECLARE @ChangeBy VARCHAR(200)='00000111'
--DECLARE @startdate VARCHAR(20)='20240101'
--DECLARE @enddate VARCHAR(20)='20241231'
--DECLARE @paygradestart VARCHAR(20)=''
--DECLARE @paygradeend VARCHAR(20)=''
--DECLARE @empstart VARCHAR(200)='00000445'
--DECLARE @empend VARCHAR(200)='00000446'
--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

--set @CreateBy =''
--set @ChangeBy =''
--set @startdate =@now
--set @enddate =@now
--set @paygradestart ='00'
--set @paygradeend ='03'
--set @empstart ='10000148'
--set @empend ='10000151'
--set @emptype ='04'
--set @empsubtype ='10'

SELECT EmployeeID,CAST(REPLACE(ISNULL(dbo.GetPEN(Amount), '0'), ',', '.') AS DECIMAL(22,0)) as Amount 
into #pa4
from PHRPA0004 
where 
	EmployeeID between @empstart and @empend 
	and WageType=(select top(1)WageType from PHRPYCU0305 where WageTypeCum='B003' and EndDate='99991231')
	and @startdate<=EndDate
	and @enddate>=StartDate

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)+5 as [Sequence]
,@startdate as StartDate
,@enddate as EndDate
,pa2.EmployeeID
,PLAF.TreatmentType
,case 
when PLAF.TreatmentType='300' then pa4.Amount
when PLAF.TreatmentType='400' then pa4.Amount
else PLAF.FixedValue end as Plafond
,case 
when PLAF.TreatmentType='300' then pa4.Amount
when PLAF.TreatmentType='400' then pa4.Amount
else PLAF.FixedValue end as PlafondRemain
,convert(varchar(50),'0.00')as PlafondUsed 
,PLAF.MedicalFamily
,pa3.PayrollGrade
into #temp
from PHRPA0002 pa2 
left join #pa4 pa4 on pa2.EmployeeID=pa4.EmployeeID
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 and pa2.EmployeeType = @emptype  and pa2.EmployeeID between @empstart and @empend 


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)+5 as [Sequence],@startdate as StartDate
,@enddate as EndDate,pa2.EmployeeID
,PLAF.TreatmentType
,case 
when PLAF.TreatmentType='300' then pa4.Amount
when PLAF.TreatmentType='400' then pa4.Amount
else PLAF.FixedValue end as Plafond
,case 
when PLAF.TreatmentType='300' then pa4.Amount
when PLAF.TreatmentType='400' then pa4.Amount
else PLAF.FixedValue end as PlafondRemain
,convert(varchar(50),'0.00')as PlafondUsed 
,PLAF.MedicalFamily
,pa3.PayrollGrade
into #temp2
from PHRPA0002 pa2 
left join #pa4 pa4 on pa2.EmployeeID=pa4.EmployeeID
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 and pa2.EmployeeID between @empstart and @empend 
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 
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

drop table #pa4