CREATE PROCEDURE [dbo].[Rpt_Mega_WTRecap_Summary_New_Header]
	@landscape varchar(3),
	@payperiod varchar(6)

AS


 --declare @landscape varchar(3) = N'100'
 --declare @payperiod varchar(6) = '201102'



DECLARE @payPeriodStartDate varchar(8)
DECLARE @payPeriodEndDate varchar(8)
DECLARE @payPeriodEndDateDt datetime

DECLARE @payPeriodStr varchar(50)


SET @payPeriodStartDate = @payPeriod + '01'
SET @payPeriodEndDateDt = DATEADD(dd, -1, DATEADD(mm, 1, CONVERT(datetime, @payPeriodStartDate)))
SET @payPeriodEndDate = @payPeriod + CONVERT(varchar(2), DATEPART(dd, @payPeriodEndDateDt))

set @payPeriodStr = DATENAME(month,@payPeriodEndDateDt) + ' ' + left(@payPeriodEndDate,4)

 declare @logo varbinary(max)
 declare @companyname varchar(50)
 
 SELECT TOP 1
       @companyname = [description]
      ,@logo = [logo]
  FROM [dbo].[base_cust_ref_companycode]  
  where landscape = @landscape 
 
declare @group_by varchar(50)
declare @group_by_colname varchar(50)

select top 1 @group_by = col_id, @group_by_colname = descr
from dbo.hr_cust_py_report_additional 
where	landscape = @landscape 
  and	rep_id = 'WT03R' 
  and	start_date <= @payPeriodEndDate 
  and	end_date >= @payPeriodEndDate 
  and	is_grouped = 1

if @group_by is null
begin

set @group_by  = 'cost_center_descr'
set @group_by_colname  = 'Cost Center Descr'

end

  
  
  declare @hr_cust_py_report TABLE (
	[landscape] [varchar](4) NOT NULL,
	[rep_id] [varchar](50) NOT NULL,
	[row] [int] NOT NULL,
	[col_no] [int] NOT NULL,
	[seq_no] [int] NOT NULL,
	[wage_type] [varchar](5) NULL,
	[descr] [varchar](50) NULL
)
  
insert into @hr_cust_py_report
SELECT [landscape]
      ,[rep_id]
      ,[row]
      ,[col_no]
      ,[seq_no]
      ,[wage_type]
      ,[descr]
  FROM [dbo].[hr_cust_py_report] WHERE start_date <= @payPeriodEndDate
  AND end_date >= @payPeriodEndDate and rep_id = 'WT01R'
  
     declare @hr_cust_py_report_col_name TABLE (
	[landscape] [varchar](4)  NULL,
	[rep_id] [varchar](50) NULL,
	[row] [int]  NULL,
	[col_no] [int]  NULL,
	column_name varchar(6))

insert into @hr_cust_py_report_col_name	
select *,
'WT' + right('00' + convert(varchar,(ROW_NUMBER () OVER (ORDER BY landscape, rep_id, row, col_no) + 49) / 50) ,2) 
+ right('00' + convert(varchar,((ROW_NUMBER () OVER (ORDER BY landscape, rep_id, row, col_no) -1) % 50)+1) ,2) as column_name
 from(
  select distinct landscape, rep_id, row, col_no from @hr_cust_py_report )a ORDER BY landscape, rep_id, row, col_no
  
----//---- cek kolom ADF buat header dari py_report_additional
----select top 1000 'ADF' + right('00' + convert(varchar,ROW_NUMBER() OVER (ORDER BY col_no)),2) as column_id ,case is_visible when 1 then descr else '' end as description from dbo.hr_cust_py_report_additional where landscape = @landscape and rep_id = 'WT01R' and start_date <= @payPeriodEndDate and end_date >= @payPeriodEndDate order by col_no	
----//----

	
  
  declare @row int
  declare @col int
  declare @column_name varchar(6)
  set @row = 0
  set @col = 0
  while @row < 4
  begin
  set @row = @row + 1
  set @col = 0
    while @col < 50
		begin
		set @col = @col + 1
		set @column_name = null
		
		select @column_name=column_name  from @hr_cust_py_report_col_name
		where column_name = 'WT' + right('00' + convert(varchar, @row),2) + right('00' + convert(varchar, @col),2)
if @column_name is null or @column_name = ''
begin
insert into @hr_cust_py_report_col_name
select @landscape,'',0,0,'WT' + right('00' + convert(varchar, @row),2) + right('00' + convert(varchar, @col),2)
end



		end
		
	end
	
	    declare @hr_cust_py_report_desc TABLE (
	[landscape] [varchar](4) NOT NULL,
	[rep_id] [varchar](50) NOT NULL,
	[row] [int] NOT NULL,
	[col_no] [int] NOT NULL,
	column_name varchar(6),
	[description] [varchar](50) NULL)
	
	insert into @hr_cust_py_report_desc
	select a.*, b.descr from @hr_cust_py_report_col_name as a
	left join(
	select MAX(landscape) as landscape, MAX(rep_id) as rep_id, MAX(row) as row, MAX(col_no) as col_no,MAX(descr) as descr
	 from @hr_cust_py_report group by landscape, rep_id, row,col_no)as b
	 on a.landscape =b.landscape and 
	 a.rep_id=b.rep_id and
	 a.row=b.row and
	 a.col_no=b.col_no

  select * from (
  select  @companyname as companyname, @logo as logo, @payPeriodStr as payPeriodStr, @group_by as group_by, @group_by_colname as group_by_colname, * from (select column_name, description from @hr_cust_py_report_desc)as SourceTable
PIVOT
(
  max(description)
  for column_name in  ([WT0101],[WT0102],[WT0103],[WT0104],[WT0105],[WT0106],[WT0107],[WT0108],[WT0109],[WT0110],[WT0111],[WT0112],[WT0113],[WT0114],[WT0115],[WT0116],[WT0117],[WT0118],[WT0119],[WT0120],[WT0121],[WT0122],[WT0123],[WT0124],[WT0125],[WT0126],[WT0127],[WT0128],[WT0129],[WT0130],[WT0131],[WT0132],[WT0133],[WT0134],[WT0135],[WT0136],[WT0137],[WT0138],[WT0139],[WT0140],[WT0141],[WT0142],[WT0143],[WT0144],[WT0145],[WT0146],[WT0147],[WT0148],[WT0149],[WT0150],
			[WT0201],[WT0202],[WT0203],[WT0204],[WT0205],[WT0206],[WT0207],[WT0208],[WT0209],[WT0210],[WT0211],[WT0212],[WT0213],[WT0214],[WT0215],[WT0216],[WT0217],[WT0218],[WT0219],[WT0220],[WT0221],[WT0222],[WT0223],[WT0224],[WT0225],[WT0226],[WT0227],[WT0228],[WT0229],[WT0230],[WT0231],[WT0232],[WT0233],[WT0234],[WT0235],[WT0236],[WT0237],[WT0238],[WT0239],[WT0240],[WT0241],[WT0242],[WT0243],[WT0244],[WT0245],[WT0246],[WT0247],[WT0248],[WT0249],[WT0250],
			[WT0301],[WT0302],[WT0303],[WT0304],[WT0305],[WT0306],[WT0307],[WT0308],[WT0309],[WT0310],[WT0311],[WT0312],[WT0313],[WT0314],[WT0315],[WT0316],[WT0317],[WT0318],[WT0319],[WT0320],[WT0321],[WT0322],[WT0323],[WT0324],[WT0325],[WT0326],[WT0327],[WT0328],[WT0329],[WT0330],[WT0331],[WT0332],[WT0333],[WT0334],[WT0335],[WT0336],[WT0337],[WT0338],[WT0339],[WT0340],[WT0341],[WT0342],[WT0343],[WT0344],[WT0345],[WT0346],[WT0347],[WT0348],[WT0349],[WT0350],
			[WT0401],[WT0402],[WT0403],[WT0404],[WT0405],[WT0406],[WT0407],[WT0408],[WT0409],[WT0410],[WT0411],[WT0412],[WT0413],[WT0414],[WT0415],[WT0416],[WT0417],[WT0418],[WT0419],[WT0420],[WT0421],[WT0422],[WT0423],[WT0424],[WT0425],[WT0426],[WT0427],[WT0428],[WT0429],[WT0430],[WT0431],[WT0432],[WT0433],[WT0434],[WT0435],[WT0436],[WT0437],[WT0438],[WT0439],[WT0440],[WT0441],[WT0442],[WT0443],[WT0444],[WT0445],[WT0446],[WT0447],[WT0448],[WT0449],[WT0450]))AS PivotTable) as a cross join 
	
	(
		
SELECT ADF01	,
ADF02	,
ADF03	,
ADF04	,
ADF05	,
ADF06	,
ADF07	,
ADF08	,
ADF09	,
ADF10	,
ADF11	,
ADF12	,
ADF13	,
ADF14	,
ADF15	,
ADF16	,
ADF17	,
ADF18	,
ADF19	,
ADF20	,
ADF21	,
ADF22	,
ADF23	,
ADF24	,
ADF25	,
ADF26	,
ADF27	,
ADF28	,
ADF29	,
ADF30	,
ADF31	,
ADF32	,
ADF33	,
ADF34	,
ADF53	,
ADF54	


FROM
(select top 1000 'ADF' + right('00' + convert(varchar,ROW_NUMBER() OVER (ORDER BY col_no)),2) as column_id ,case is_visible when 1 then descr else '' end as description from dbo.hr_cust_py_report_additional where landscape = @landscape and rep_id = 'WT03R' and start_date <= @payPeriodEndDate and end_date >= @payPeriodEndDate order by col_no
) AS SourceTable
PIVOT
(
max(description)
FOR column_id IN (
ADF01	,
ADF02	,
ADF03	,
ADF04	,
ADF05	,
ADF06	,
ADF07	,
ADF08	,
ADF09	,
ADF10	,
ADF11	,
ADF12	,
ADF13	,
ADF14	,
ADF15	,
ADF16	,
ADF17	,
ADF18	,
ADF19	,
ADF20	,
ADF21	,
ADF22	,
ADF23	,
ADF24	,
ADF25	,
ADF26	,
ADF27	,
ADF28	,
ADF29	,
ADF30	,
ADF31	,
ADF32	,
ADF33	,
ADF34	,
ADF53	,
ADF54		

	


)
) AS PivotTable
	
	)	as b