USE [MinovaHR_ESS_Mega_Syariah_Production]
GO
/****** Object:  StoredProcedure [dbo].[Rpt_BMS_HR_PY_Bank_Transfer_Content]    Script Date: 11/18/2021 9:23:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Rpt_BMS_HR_PY_Bank_Transfer_Content] 
	@landscape AS VARCHAR(3),
	@companycode AS VARCHAR(5),
	@paygroup AS VARCHAR(5),
	@payperiod AS VARCHAR(6),
	@bank_id AS VARCHAR(2),
	@tgltrans AS VARCHAR(8),
	@key1 AS NVARCHAR(MAX),
	@key2 AS NVARCHAR(MAX)
AS

--DECLARE @landscape AS VARCHAR(3) 
--DECLARE @companycode AS VARCHAR(5) 
--DECLARE @paygroup AS VARCHAR(5) 
--DECLARE @payperiod AS VARCHAR(6) 
--DECLARE @bank_id AS VARCHAR(2) 
--DECLARE @tgltrans AS VARCHAR(8) 
--DECLARE @emp_type VARCHAR(12)
--SET @landscape = '100'
--SET @companycode = '1000' 
--SET @paygroup = '01'
--SET @payperiod = '202107'
--SET @bank_id = '11' 
--SET @tgltrans = '20211118'
--SET @emp_type = ''

DECLARE @companyname AS NVARCHAR(50)
DECLARE @companylogo AS VARBINARY(MAX)
DECLARE @payrollgroup AS NVARCHAR(50)
DECLARE @paymonth AS NVARCHAR(2)
DECLARE @payyear AS NVARCHAR(4)
DECLARE @begda AS NVARCHAR(8)
DECLARE @endda AS NVARCHAR(8)
DECLARE @enddate AS VARCHAR(8)
SET @paymonth = RIGHT(@payperiod,2)
SET @payyear = LEFT(@payperiod,4)
SET @begda = @payperiod + '01'
SET @endda = convert(NVARCHAR(8),dateadd(d,-1, dateadd(m, 1, @begda)), 112)

DECLARE @last_date AS varchar(12) 
DECLARE @last_year AS varchar(8)
DECLARE @last_month AS varchar(8)
SET @last_date = convert(VARCHAR(6), dateadd(d,0, dateadd(m,-1, @begda)), 112)
SET @last_year = LEFT(@last_date,4)
SET @last_month = RIGHT(@last_date,2)

-----//----- tabel-tabel internal
DECLARE @tbl_emp TABLE (
	emp_id varchar(8)
	,emp_name varchar(250)
	,nick_name varchar(250)
	,[bank_id] [varchar](2) 
	,[bank_account] [varchar](50) 
	,[pay_grade] [varchar](8) 
	,[tax_status] [varchar](8) 
	,[npwp] [varchar](50) 
	,[jamsostek_type] [varchar](4) 
	,[jamsostek] [varchar](50) 
	,[jamsostek_level] [varchar](5)
	,bank_acc_name VARCHAR(100)
	,[bank_id_non] [varchar](20) 
	,[bank_name_non] [varchar](50) 
	,[bank_account_non] [varchar](50) 
	,[cost_center] [varchar](8) 
	,[employee_area] [varchar](4) 
	,[employee_office] [varchar](4) 
	,[employee_status] [varchar](8) 
	,[employee_type] [varchar](8) 
	,[employee_subtype] [varchar](8) 
	,[payroll_group] [varchar](8) 
	,[organization] [varchar](8) 
	,[position] [varchar](8) 
	,[job] [varchar](8) 
	,bank_name varchar(250) 
	)
	
DECLARE @tbl_py TABLE (
	emp_id varchar(8)
	,this_amount decimal(18,0)
	,wage_type varchar(8)
	)

DECLARE @tbl_py_last TABLE (
	emp_id varchar(8)
	,last_amount decimal(18,0)
	)

DECLARE @tbl_tr_enc TABLE
	(landscape varchar (3),
	 employee_id varchar(8),
	 payPeriodMonth varchar(2),
	 payPeriodYear varchar(4),
	 runPeriodMonth varchar(2),
	 runPeriodYear varchar(4),
	 wage_type varchar(4),
	 amount varchar(250),
	 rate varchar(250),
	 flg_retro varchar(3),
	 flg_period VARCHAR(20))

DECLARE @tbl_tr_dec TABLE
	(landscape varchar (3),
	 employee_id varchar(8),
	 payPeriodMonth varchar(2),
	 payPeriodYear varchar(4),
	 runPeriodMonth varchar(2),
	 runPeriodYear varchar(4),
	 wage_type varchar(4),
	 amount DECIMAL(20,0),
	 rate DECIMAL(20,0),
	 flg_retro varchar(3),
	 flg_period VARCHAR(20))

DECLARE @tbl_tr_dec_sum TABLE
	(landscape varchar (3),
	 employee_id varchar(8),
	 payPeriodMonth varchar(2),
	 payPeriodYear varchar(4),
	 runPeriodMonth varchar(2),
	 runPeriodYear varchar(4),
	 wage_type varchar(4),
	 amount DECIMAL(20,0),
	 rate DECIMAL(20,0),
	 flg_retro varchar(1),
	 flg_period VARCHAR(20))

DECLARE @t_res TABLE
(
	emp_id varchar(8)
	,emp_name varchar(250)
	,nick_name varchar(250)
	,[bank_account] [varchar](50) 
	,bank_name VARCHAR(200)
	,bank_acc_name VARCHAR(225)
	,bank_name_non VARCHAR(200)
	,bank_acc_non VARCHAR(50)
	,this_amount DECIMAL(20,0)
	,last_amount DECIMAL(20,0)
	,selisih VARCHAR(50)
	,persen VARCHAR(50)
	,persenpositif  VARCHAR(50)
	,jum_emp DECIMAL(18,0)
)	

DECLARE @t_res_temp TABLE
(
	emp_id varchar(8)
	,emp_name varchar(250)
	,nick_name varchar(250)
	,[bank_account] [varchar](50) 
	,bank_name VARCHAR(200)
	,bank_acc_name VARCHAR(225)
	,bank_name_non VARCHAR(200)
	,bank_acc_non VARCHAR(50)
	,this_amount DECIMAL(20,0)
	,last_amount DECIMAL(20,0)
	,selisih VARCHAR(50)
	,persen VARCHAR(50)
	,persenpositif  VARCHAR(50)
	,jum_emp DECIMAL(18,0)
) 

SELECT @companyname = description
	 , @companylogo = logo
FROM
	base_cust_ref_companycode
WHERE
	(landscape = @landscape)
	AND (companycode = @companycode)

SELECT @payrollgroup = description
FROM
	base_cust_ref_payroll_group
WHERE
	(landscape = @landscape)
	AND (payroll_group = @paygroup)

-----//----- pengambilan data employee
INSERT INTO @tbl_emp 	
SELECT  tr300.employee_id
		,md1.full_name
		,md1.nick_name
		,md3.[bank_id]
		,CASE WHEN md3.[bank_account] <> '' THEN md3.[bank_account] ELSE '0' END 
		,md3.[pay_grade]
		,md3.[tax_status]
		,md3.[npwp]
		,md3.[jamsostek_type]
		,md3.[jamsostek]
		,md3.[jamsostek_level]
		,md3.bank_account_name
		,CASE WHEN md3.bank_id_non <> '' THEN md3.bank_id_non ELSE '0' END 
		,bank_non.description AS bank__name_non
		,md3.bank_account_non
		,tr300.[cost_center]
		,tr300.[employee_area]
		,tr300.[employee_office]
		,tr300.[employee_status]
		,tr300.[employee_type]
		,tr300.[employee_subtype]
		,tr300.[payroll_group]
		,tr300.[organization]
		,tr300.[position]
		,tr300.[job]
		,bank.description 
FROM hr_tr0300 AS tr300
LEFT JOIN hr_md_emp_md0001 AS md1
	ON tr300.landscape = md1.landscape
	AND tr300.employee_id = md1.emp_id 
	AND md1.start_date <= @endda AND md1.end_date >= @endda 
LEFT JOIN hr_md_emp_md0003 AS md3
	ON tr300.landscape = md3.landscape
	AND tr300.employee_id = md3.emp_id 
	AND md3.start_date <= @endda AND md3.end_date >= @endda 
LEFT JOIN base_cust_ref_bankid AS bank
	ON bank.code = md3.bank_id 
	AND bank.landscape = md3.landscape 
LEFT JOIN dbo.base_cust_ref_bankid AS bank_non
	ON bank_non.landscape = md3.landscape
	AND bank_non.code = md3.bank_id_non
WHERE  tr300.landscape = @landscape
	AND tr300.payroll_group = @paygroup 
	AND tr300.pay_period_month = @paymonth AND tr300.pay_period_year = @payyear
	AND tr300.run_period_month = @paymonth AND tr300.run_period_year = @payyear 
	AND (tr300.company_id = @companycode OR @companycode = '')
	--AND md3.bank_id = @bank_id 
	--AND tr300.employee_id = '00005311'
	--AND (tr300.employee_type = @emp_type OR @emp_type = '')

--------------- get data payroll --------------
INSERT INTO @tbl_tr_enc
SELECT  pr.landscape , pr.employee_id, 
		pr.pay_period_month, pr.pay_period_year, 
	    pr.run_period_month, pr.run_period_year, 
	    pr.wage_type, pr.amount, pr.rate, 'N', 'now'
FROM hr_tr0301 pr  WITH (NOLOCK)
	INNER JOIN @tbl_emp emp
		ON pr.employee_id = emp.emp_id
WHERE pr.pay_period_month = @paymonth AND pr.pay_period_year = @payyear 
	AND pr.run_period_month = @paymonth AND pr.run_period_year = @payyear 
	AND pr.wage_type in ('THP','D050')
	
INSERT INTO @tbl_tr_enc
SELECT	pr.landscape, pr.employee_id, 
		pr.pay_period_month, pr.pay_period_year, 
		pr.run_period_month, pr.run_period_year, 
		pr.wage_type, pr.amount, pr.rate, 'R', 'now'
FROM hr_tr0301_retro pr  WITH (NOLOCK)
	INNER JOIN @tbl_emp emp
		ON pr.employee_id = emp.emp_id
WHERE pr.pay_period_month = @paymonth AND pr.pay_period_year = @payyear 
	AND pr.run_period_month = @paymonth AND pr.run_period_year = @payyear 
	AND pr.wage_type in ('THP','D050')
	 
INSERT INTO @tbl_tr_dec
SELECT pr.landscape, pr.employee_id, 
	   pr.payPeriodMonth, pr.payPeriodYear, 
	   pr.runPeriodMonth, pr.runPeriodYear, pr.wage_type,
	   CONVERT(decimal(18,0), dbo.SDE(pr.amount, 'M!N0V@2010')),
	   CONVERT(decimal(18,0), dbo.SDE(pr.rate, 'M!N0V@2010')),  pr.flg_retro, pr.flg_period
FROM @tbl_tr_enc pr

INSERT INTO @tbl_tr_dec_sum
SELECT tbl_tr_dec.landscape, employee_id,
	   payPeriodMonth, payPeriodYear,
	   runPeriodMonth, runPeriodYear, wage_type,
	   SUM(amount) AS amount,SUM(rate) AS rate, 
	   min(flg_retro) AS flg_retro, flg_period
FROM @tbl_tr_dec AS tbl_tr_dec
GROUP BY tbl_tr_dec.landscape, employee_id, 
		 payPeriodMonth, payPeriodYear,
		 runPeriodMonth,runPeriodYear, wage_type, flg_period


-----//----- data payroll bulan ini
INSERT INTO @tbl_py 
SELECT  tbl_tr_dec_sum.employee_id
		,tbl_tr_dec_sum.amount 'amount'
		,tbl_tr_dec_sum.wage_type
FROM @tbl_tr_dec_sum AS tbl_tr_dec_sum WHERE flg_period = 'now'


DECLARE @jum_emp DECIMAL(5,0)
SET @jum_emp = ( SELECT COUNT(emp_id) FROM @tbl_py)

--SELECT * FROM @tbl_emp

IF ( @bank_id = 'upin') ------ bank mega syariah ------
BEGIN 
	INSERT INTO @t_res
	SELECT DISTINCT  tbl_emp.emp_id 
			,tbl_emp.emp_name
			,tbl_emp.nick_name
			,tbl_emp.bank_account 
			,tbl_emp.bank_name 
			,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
			,tbl_emp.bank_name_non
			,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
			,isnull(tbl_py.this_amount, 0) AS this_amount
			,isnull(tbl_py_last.last_amount,0) AS last_mount
			,'' AS selisih
			,'' AS persen
			,'' AS persenpositif
			,1 AS jum_emp
	FROM @tbl_emp AS tbl_emp
	INNER JOIN @tbl_py AS tbl_py
		ON tbl_py.emp_id = tbl_emp.emp_id 
	LEFT JOIN @tbl_py_last AS tbl_py_last
		ON tbl_py_last.emp_id = tbl_emp.emp_id 
	WHERE tbl_emp.bank_account  <> '' AND tbl_emp.bank_account_non <> ''
		AND tbl_py.wage_type = 'D050' AND this_amount > 0
		
	INSERT INTO @t_res
	SELECT DISTINCT  tbl_emp.emp_id 
			,tbl_emp.emp_name
			,tbl_emp.nick_name 
			,tbl_emp.bank_account 
			,tbl_emp.bank_name 
			,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
			,tbl_emp.bank_name_non
			,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
			,isnull(tbl_py.this_amount, 0) AS this_amount
			,isnull(tbl_py_last.last_amount,0) AS last_mount
			,'' AS selisih
			,'' AS persen
			,'' AS persenpositif
			,1 AS jum_emp
	FROM @tbl_emp AS tbl_emp
	INNER JOIN @tbl_py AS tbl_py
		ON tbl_py.emp_id = tbl_emp.emp_id 
	LEFT JOIN @tbl_py_last AS tbl_py_last
		ON tbl_py_last.emp_id = tbl_emp.emp_id 
	WHERE tbl_emp.bank_account  <> '' AND ( tbl_emp.bank_account_non = NULL  OR tbl_emp.bank_account_non = '' OR tbl_emp.bank_account_non = '0')
		AND tbl_py.wage_type = 'THP'
		
	INSERT INTO @t_res
	SELECT DISTINCT  tbl_emp.emp_id 
			,tbl_emp.emp_name
			,tbl_emp.nick_name 
			,tbl_emp.bank_account 
			,tbl_emp.bank_name 
			,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
			,tbl_emp.bank_name_non
			,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
			,isnull(tbl_py.this_amount, 0) AS this_amount
			,isnull(tbl_py_last.last_amount,0) AS last_mount
			,'' AS selisih
			,'' AS persen
			,'' AS persenpositif
			,1 AS jum_emp
	FROM @tbl_emp AS tbl_emp
	INNER JOIN @tbl_py AS tbl_py
		ON tbl_py.emp_id = tbl_emp.emp_id 
	LEFT JOIN @tbl_py_last AS tbl_py_last
		ON tbl_py_last.emp_id = tbl_emp.emp_id 
	WHERE tbl_emp.bank_account  <> '0' AND tbl_emp.bank_account_non <> '0' AND this_amount < 1000000
		AND tbl_py.wage_type = 'THP' AND tbl_emp.emp_id NOT IN ( SELECT DISTINCT emp_id FROM @t_res)	

END
ELSE IF ( @bank_id <> '') ------ bank mega------
BEGIN
	INSERT INTO @t_res_temp
	SELECT DISTINCT  tbl_emp.emp_id 
			,tbl_emp.emp_name
			,tbl_emp.nick_name 
			,tbl_emp.bank_account 
			,tbl_emp.bank_name 
			,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
			,tbl_emp.bank_name_non
			,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
			,isnull(tbl_py.this_amount, 0) AS this_amount
			,isnull(tbl_py_last.last_amount,0) AS last_mount
			,'' AS selisih
			,'' AS persen
			,'' AS persenpositif
			,1 AS jum_emp
	FROM @tbl_emp AS tbl_emp
	INNER JOIN @tbl_py AS tbl_py
		ON tbl_py.emp_id = tbl_emp.emp_id 
	LEFT JOIN @tbl_py_last AS tbl_py_last
		ON tbl_py_last.emp_id = tbl_emp.emp_id 
	WHERE tbl_emp.bank_account  <> '' AND tbl_emp.bank_account_non <> ''
		AND tbl_py.wage_type = 'D050' AND this_amount > 0

	INSERT INTO @t_res
	SELECT DISTINCT  tbl_emp.emp_id 
			,tbl_emp.emp_name
			,tbl_emp.nick_name 
			,tbl_emp.bank_account 
			,tbl_emp.bank_name 
			,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
			,tbl_emp.bank_name_non
			,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
			,isnull(tbl_py.this_amount, 0) AS this_amount
			,isnull(tbl_py_last.last_amount,0) AS last_mount
			,'' AS selisih
			,'' AS persen
			,'' AS persenpositif
			,1 AS jum_emp
	FROM @tbl_emp AS tbl_emp
	INNER JOIN @tbl_py AS tbl_py
		ON tbl_py.emp_id = tbl_emp.emp_id 
	LEFT JOIN @tbl_py_last AS tbl_py_last
		ON tbl_py_last.emp_id = tbl_emp.emp_id 
	WHERE (tbl_emp.bank_account_non <> '0') --AND ( tbl_emp.bank_account = NULL  OR tbl_emp.bank_account = '' OR tbl_emp.bank_account = '0')
		AND tbl_py.wage_type = 'THP'
	
	--INSERT INTO @t_res
	--SELECT DISTINCT  tbl_emp.emp_id 
	--		,tbl_emp.emp_name 
	--		,tbl_emp.bank_account 
	--		,tbl_emp.bank_name 
	--		,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
	--		,tbl_emp.bank_name_non
	--		,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
	--		,isnull(tbl_py.this_amount, 0) AS this_amount
	--		,isnull(tbl_py_last.last_amount,0) AS last_mount
	--		,'' AS selisih
	--		,'' AS persen
	--		,'' AS persenpositif
	--		,1 AS jum_emp
	--FROM @tbl_emp AS tbl_emp
	--INNER JOIN @tbl_py AS tbl_py
	--	ON tbl_py.emp_id = tbl_emp.emp_id 
	--LEFT JOIN @tbl_py_last AS tbl_py_last
	--	ON tbl_py_last.emp_id = tbl_emp.emp_id 
	--WHERE (tbl_emp.bank_account_non <> '0') AND tbl_emp.bank_account <> '0' --AND this_amount > 1000000
	--	AND tbl_py.wage_type = 'THP'
		
	INSERT INTO @t_res
	SELECT DISTINCT  tbl_emp.emp_id 
			,tbl_emp.emp_name
			,tbl_emp.nick_name 
			,tbl_emp.bank_account 
			,tbl_emp.bank_name 
			,ISNULL(tbl_emp.bank_acc_name, 'None') AS bank_acc_name
			,tbl_emp.bank_name_non
			,ISNULL(tbl_emp.bank_account_non, 'None') AS bank_acc_non
			,isnull(tbl_py.this_amount, 0) AS this_amount
			,isnull(tbl_py_last.last_amount,0) AS last_mount
			,'' AS selisih
			,'' AS persen
			,'' AS persenpositif
			,1 AS jum_emp
	FROM @tbl_emp AS tbl_emp
	INNER JOIN @tbl_py AS tbl_py
		ON tbl_py.emp_id = tbl_emp.emp_id 
	LEFT JOIN @tbl_py_last AS tbl_py_last
		ON tbl_py_last.emp_id = tbl_emp.emp_id 
	WHERE (tbl_emp.bank_account_non <> '0') AND tbl_emp.bank_account <> '0' --AND this_amount > 1000000
		AND tbl_py.wage_type = 'THP'
		AND tbl_emp.emp_id IN ( SELECT DISTINCT emp_id FROM @t_res_temp)
		
END

SELECT DISTINCT * FROM @t_res