ALTER procedure [dbo].[SPTBulanan_Bupot_Resign_Coretax]
	@landscape varchar(3),
	@company_code varchar(4),
	@year varchar(4),
	@tax_office varchar(8),
	@employee_status varchar(2) = null,
	@payroll_group varchar(2) = null,
	@emp_id_beg varchar(8) = null,
	@emp_id_end varchar(8) = NULL
AS

--DECLARE @landscape varchar(3) = '100'
--DECLARE @company_code varchar(4) = '1000'
--DECLARE @year varchar(4) = '2025'
--DECLARE @tax_office varchar(8) = ''
--DECLARE @employee_status varchar(2) = ''
--DECLARE @payroll_group varchar(2) = '13'
--DECLARE @emp_id_beg varchar(8) = '20241073'
--DECLARE @emp_id_end varchar(8) = '20241073'

-- Extract year and month from @nowperiod
DECLARE @nowmonth varchar (2) = dbo.fn_formatdatetime(GETDATE(),'mm')
DECLARE @nowperiod varchar (6) = dbo.fn_formatdatetime(GETDATE(),'yyyymm')

-- Calculate the next month
DECLARE @years INT = CAST(SUBSTRING(@nowperiod, 1, 4) AS INT);
DECLARE @months INT = CAST(SUBSTRING(@nowperiod, 5, 2) AS INT);
SET @months = @months + 1;
DECLARE @next_month VARCHAR(6) = CAST(@year AS VARCHAR(4)) + RIGHT('0' + CAST(@months AS VARCHAR(2)), 2);

-- Temporary tables for final SELECT
select distinct emp_subarea into #emp_subarea from base_cust_ref_emp_office with (nolock) where tax_office=@tax_office
select distinct emp_id,start_date,end_date,npwp,npwp_date,tax_status into #md3 from hr_md_emp_md0003 with (nolock) where end_date='99991231'
select distinct emp_id,start_date,end_date,full_name,ktp,gender into #md1 from hr_md_emp_md0001 with (nolock) where end_date='99991231'
select distinct emp_id,start_date,end_date,address_type,street into #md6 from hr_md_emp_md0006 with (nolock) where end_date='99991231' and address_type='01'
select distinct emp_id,hiring_date into #md15 from hr_md_emp_md0015 with (nolock)


-- Result
select distinct
	emp_id
	,start_date
	,end_date
	,position
	,employee_type
	,employee_status
	,employee_office
	,movement_type
into #result
from hr_md_emp_md0002 with (nolock) 
where 
	 @year+'1231' between start_date and end_date
	and (payroll_group=@payroll_group or @payroll_group='')
	 and (emp_id>=@emp_id_beg or @emp_id_beg='')
	and (emp_id<=@emp_id_end or @emp_id_end='')
	--and (employee_office in (select emp_subarea from #emp_subarea) or @tax_office='')
	and ((employee_status in ('03','04') and left(start_date,4)=@year) or (employee_status in ('01','02')))

	----select * from  #result

-- Result_
select distinct 
r.emp_id
,r.employee_status
,case 
	when left(min(md2.start_date),4)=@year then right(left(min(md2.start_date),6),2) -- Employee Aktif
	when  left(min(md2.start_date),4)=@year AND r.employee_status IN ('03', '04') THEN
	CASE
			WHEN RIGHT(min(md2.start_date), 1) = '1' THEN 
				-- Subtract 1 from the month, ensuring it doesn't go below 01
				RIGHT(CONCAT('0', 
					CASE 
						WHEN CAST(RIGHT(LEFT(min(md2.start_date), 6), 2) AS INT) - 1 < 1 
						THEN 1 
						ELSE CAST(RIGHT(LEFT(min(md2.start_date), 6), 2) AS INT) - 1 
					END
				), 2) -- Employee Resign di awal bulan
			ELSE RIGHT(LEFT(min(md2.start_date), 6), 2) -- Employee Resign di tengah bulan
		END
	else '01'
end as start_date
,CASE 
	WHEN LEFT(MAX(md2.end_date), 4) = @year THEN RIGHT(LEFT(MAX(md2.end_date), 6), 2) -- Employee Aktif
	WHEN LEFT(MAX(md2.start_date), 4) = @year AND r.employee_status IN ('03', '04') THEN 
		CASE
			WHEN RIGHT(MAX(md2.start_date), 1) = '1' THEN 
				-- Subtract 1 from the month, ensuring it doesn't go below 01
				RIGHT(CONCAT('0', 
					CASE 
						WHEN CAST(RIGHT(LEFT(MAX(md2.start_date), 6), 2) AS INT) - 1 < 1 
						THEN 1 
						ELSE CAST(RIGHT(LEFT(MAX(md2.start_date), 6), 2) AS INT) - 1 
					END
				), 2) -- Employee Resign di awal bulan
			ELSE RIGHT(LEFT(MAX(md2.start_date), 6), 2) -- Employee Resign di tengah bulan
		END
	ELSE '12'
END as end_date
,office.tax_office AS employee_office
,r.movement_type
into #result_
from #result r
left join hr_md_emp_md0002 md2 on r.emp_id=md2.emp_id
LEFT JOIN base_cust_ref_emp_office as office
	ON md2.employee_office = office.emp_subarea
where 
@year between left(md2.start_date,4) and left(md2.end_date,4)
group by r.emp_id,office.tax_office,r.employee_status,r.movement_type

--	--select * from #result_ where emp_id='20231441'

--Result_2
SELECT 
    emp_id,
    start_date,
    end_date,
	employee_status,
    employee_office,
	CASE 
		WHEN movement_type='54' THEN 'Annualized'
		WHEN employee_status in('04','03','02') THEN 'Partial Year'
	ELSE 'Full Year'
	END AS status_bukti_potong,
    -- Calculate the number of months between start_date and end_date
	CASE 
		WHEN employee_status in('04','03','02') THEN 0
		ELSE
			CASE 
			WHEN CAST(start_date AS INT) <= CAST(end_date AS INT) THEN 
				CAST(end_date AS INT) - CAST(start_date AS INT) + 1
			ELSE 
				(12 - CAST(start_date AS INT) + 1) + CAST(end_date AS INT)
		END
	END AS number_of_months
INTO #result_2
FROM 
    #result_;

	--select * from #result_2 where emp_id='20231441'


-- Tr301 Empoff
;WITH RankedData AS (
    SELECT 
        r_.emp_id,
        r_.start_date,
        r_.end_date,
        r_.employee_office,
        cum.wage_type,
        CONVERT(DECIMAL(18, 0), dbo.SDE(cum.amount, 'M!N0V@2010')) AS amount,
        cum.pay_period_month,
        DENSE_RANK() OVER (
            PARTITION BY r_.emp_id, r_.end_date 
            ORDER BY 
                CASE 
                    WHEN cum.pay_period_month = r_.end_date THEN 1 
                    ELSE 2 
                END,
                cum.pay_period_month DESC
        ) AS rn
    FROM 
        #result_ r_
    LEFT JOIN 
        hr_tr0301_cum cum 
        ON cum.employee_id = r_.emp_id 
           AND cum.pay_period_month = cum.run_period_month
           AND cum.pay_period_year = cum.run_period_year 
           AND cum.pay_period_year = @year
	WHERE cum.amount is not null
)
, CalculatedData AS (
    SELECT 
        emp_id,
        start_date,
        end_date,
        employee_office,
        wage_type,
        amount,
        pay_period_month,
        amount - LAG(amount, 1, 0) OVER (
            PARTITION BY emp_id, wage_type 
            ORDER BY pay_period_month
        ) AS amount_difference
    FROM 
        RankedData
    WHERE 
        rn = 1
)
SELECT DISTINCT
    emp_id,
    start_date,
    end_date,
    employee_office,
    wage_type,
    amount,
    pay_period_month,
    amount_difference
INTO #tr301_empoff
FROM 
    CalculatedData
ORDER BY 
    emp_id, 
    wage_type, 
    pay_period_month;

	--select * from #tr301_empoff

select distinct code into #a1 from hr_cu0300 where spt_no='1'
		select distinct code into #a2 from hr_cu0300 where spt_no='2'
		select distinct code into #a3 from hr_cu0300 where spt_no='3'
		select distinct code into #a4 from hr_cu0300 where spt_no='4'
		select distinct code into #a5 from hr_cu0300 where spt_no='5'
		select distinct code into #a6 from hr_cu0300 where spt_no='6'
		select distinct code into #a8 from hr_cu0300 where spt_no='8'
		select distinct code into #a10 from hr_cu0300 where spt_no='10'
		select distinct code into #a11 from hr_cu0300 where spt_no='11'
		select distinct code into #a12 from hr_cu0300 where spt_no='12'
		select distinct code into #a15 from hr_cu0300 where spt_no='15'
		select distinct code into #a20 from hr_cu0300 where spt_no='20'

		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a1_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a1) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a2_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a2) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a3_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a3) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a4_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a4) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a5_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a5) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a6_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a6) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a8_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a8) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a10_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a10) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a11_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a11) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a12_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a12) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a15_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a15) group by r.emp_id,tr.employee_office
		select distinct r.emp_id , tr.employee_office,sum(tr.amount_difference) as amount into #a20_result from #result r inner join #tr301_empoff tr on r.emp_id=tr.emp_id and tr.wage_type in (select * from #a20) group by r.emp_id,tr.employee_office


-- Final SELECT statement
select distinct
    dbo.fn_formatdatetime(GETDATE(),'dd/mm/yyyy') AS tgl_pemotongan,
'No' as pemberi_kerja,
  @year as tahun,
    r.emp_id,
    md1.ktp as npwp_pegawai,
    CASE 
        WHEN md3.tax_status = 'TK' THEN 'TK'
        WHEN md3.tax_status = 'K0' OR md3.tax_status = 'K1' OR md3.tax_status = 'K2' OR md3.tax_status = 'K3' THEN 'K' 
        WHEN md3.tax_status = 'T1' OR md3.tax_status = 'T2' OR md3.tax_status = 'T3' THEN 'HB'	
    END AS status_ptkp,
    obj.description as nama_jabatan,
    'Tidak' as karyawan_asing,
    'IDN' as kode_negara,
	r2.status_bukti_potong,
	r2.number_of_months,
    emp_type.kode_pajak as kode_objek_pajak,
    r_.employee_status,
    CAST(r_.start_date AS INT) AS masa_penghasilan_awal,
    CAST(r_.end_date AS INT) AS masa_penghasilan_akhir,
	r_.end_date,
    case when md3.npwp is null then 'NIK' else 'NPWP' end as penandatanganan_penggunaan,
	'Resident' as Nationality,
	'' as no_passport,
    '0' as Zakat,
	'NO' as OpsiGrossUp,
    r_.employee_office,
	empoff.remarks,
    case when a1.amount is null then 0 else a1.amount end as a1,
    case when a2.amount is null then 0 else a2.amount end as a2,
    case when a3.amount is null then 0 else a3.amount end as a3,
    case when a4.amount is null then 0 else a4.amount end as a4,
    case when a5.amount is null then 0 else a5.amount end as a5,
    case when a6.amount is null then 0 else a6.amount end as a6,
    case when a8.amount is null then 0 else a8.amount end as a7,
    case when a12.amount is null then 0 else a12.amount end as a10,
    case when REPLACE(LTRIM(REPLACE(case when left(md15.hiring_date,4)=@year then right(left(md15.hiring_date,6),2) else '01' end, '0', ' ')), ' ', '0')>1 then case when a20.amount is null then 0 else a20.amount end else 0 end as a18,
    case 
        when tax_status ='K0' then 'K/0' 
        when tax_status ='K1' then 'K/1'
        when tax_status ='K2' then 'K/2'
        when tax_status ='K3' then 'K/3'
        when tax_status ='T1' then 'TK/1'
        when tax_status ='T2' then 'TK/2'
        when tax_status ='T3' then 'TK/3'
        when tax_status ='TK' then 'TK/0'
    end as kodeptkp,
    'N/A' as Fasilitas,
    '' as noFasilitas,
	0 AS NoBuktiPotongSebelum
from #result_ r_
left join #result r on r.emp_id=r_.emp_id 
left join #result_2 r2 on r.emp_id=r2.emp_id and r2.employee_office=r_.employee_office and r2.employee_status=r_.employee_status
left join #md3 md3 on r.emp_id=md3.emp_id
left join #md1 md1 on r.emp_id=md1.emp_id
left join #md6 md6 on r.emp_id=md6.emp_id
left join #md15 md15 on r.emp_id=md15.emp_id
left join hr_md_orm_object obj on r.position=obj.object and obj.end_date='99991231' and obj.class='P'
left join base_cust_ref_emp_type emp_type on r.employee_type= emp_type.emp_type
left join base_cust_ref_emp_office office on r.employee_office=office.emp_subarea
left join #a1_result a1 on a1.emp_id=r.emp_id and r_.employee_office=a1.employee_office
left join #a2_result a2 on a2.emp_id=r.emp_id and r_.employee_office=a2.employee_office
left join #a3_result a3 on a3.emp_id=r.emp_id and r_.employee_office=a3.employee_office
left join #a4_result a4 on a4.emp_id=r.emp_id and r_.employee_office=a4.employee_office
left join #a5_result a5 on a5.emp_id=r.emp_id and r_.employee_office=a5.employee_office
left join #a6_result a6 on a6.emp_id=r.emp_id and r_.employee_office=a6.employee_office
left join #a8_result a8 on a8.emp_id=r.emp_id and r_.employee_office=a8.employee_office
left join #a12_result a12 on a12.emp_id=r.emp_id and r_.employee_office=a12.employee_office
left join #a20_result a20 on a20.emp_id=r.emp_id and r_.employee_office=a20.employee_office
left join base_cust_ref_emp_office empoff on r_.employee_office= empoff.emp_subarea
where (r_.end_date<=@nowmonth or  @year< dbo.fn_formatdatetime(GETDATE(),'yyyy'))
order by r.emp_id

-- Drop temporary tables

drop table #result
drop table #result_
drop table #result_2
drop table #md1
drop table #md3
drop table #md6
drop table #md15
drop table #emp_subarea
drop table #a1
drop table #a2
drop table #a3
drop table #a4
drop table #a5
drop table #a6
drop table #a8
drop table #a10
drop table #a1_result
drop table #a2_result
drop table #a3_result
drop table #a4_result
drop table #a5_result
drop table #a6_result
drop table #a8_result
drop table #a10_result
drop table #a11
drop table #a11_result
drop table #a12
drop table #a12_result
drop table #a15
drop table #a15_result
drop table #a20
drop table #a20_result
drop table #tr301_empoff