dbo.RPT_DYNAMIC_MUTASI_MESIN_TEST

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE procedure dbo.RPT_DYNAMIC_MUTASI_MESIN_TEST
@PARAMETER xml 
--with encryption
as
declare @xStsnoheader varchar(2),
  @xStsno varchar(2),
  @xStsdes varchar(100)
set @xStsnoheader='03'
set @xStsno='00';
set @xStsdes='sukses'

declare @XmlSts xml
declare @raw xml

declare @rowspage int,
  @page int,
  @desc int,
  @sort varchar(255) ,
     @start_date date,
    @end_date date,
     @start_date_str varchar(255),
    @end_date_str  varchar(255),
   @mode int,
  @format varchar(255),
  @ms_type_id int
  

 
SELECT
 @rowspage=isnull(tempTable.xItem.value('@rowspage', 'int'),0) ,
 @page=isnull(tempTable.xItem.value('@page', 'int'),1) ,
 @desc=isnull(tempTable.xItem.value('@desc', 'bit'),0) ,
 @sort=isnull(tempTable.xItem.value('@sort', 'varchar(255)'),'r1') ,
 @start_date_str =isnull(tempTable.xItem.value('start_date[1]', 'varchar(255)') ,'')  ,
 @end_date_str= isnull(tempTable.xItem.value('end_date[1]', 'varchar(255)'),'')   ,
 @mode=isnull(tempTable.xItem.value('mode[1]', 'int'),1)  ,
  @format=isnull(tempTable.xItem.value('format[1]', 'varchar(255)'),1) ,
  @ms_type_id=isnull(tempTable.xItem.value('source[1]', 'int'),-1)  
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)
declare @XMLMAPING xml
 --3011 Report Pabean Masuk Export to XLS False
 --3012 Report Pabean Keluar Export to XLS False
 --3013 Report WIP Export to XLS False
 --3014 Report Mutasi Bahan Baku Export to XLS False
 --3015 Report Mutasi Barang Jadi Export to XLS False
 --3016 Report Mutasi Scrap Export to XLS False
 --3017 Report Mutasi Mesin Export to XLS False
if @format =''
set @format='pdf'

declare @xlsreadonly bit
set @xlsreadonly=1
declare @additional_id int
declare @additional_value int

--if @mode=2 and upper(ltrim(rtrim(@format)))<>'PDF'
--begin
-- if upper(ltrim(rtrim(@format)))='XLS' or upper(ltrim(rtrim(@format)))='XLSX'
-- begin

-- set @additional_id=3010
-- set @additional_value= dbo.additional_info(@additional_id)
-- if @additional_value =1
-- begin
-- set @xlsreadonly=0
-- end

-- set @additional_id=3014
-- set @additional_value= dbo.additional_info(@additional_id)
-- if @additional_value =0
-- begin
-- set @xStsno='06'
-- set @xStsdes='Module Export to XLS/XLSX belum diaktifkan'
-- goto f
-- end
-- end
-- else
-- begin

-- set @xStsno='06'
-- set @xStsdes='Module Export to ' + @format + ' belum diaktifkan'
-- goto f

-- end
--end
 
set @start_date=dbo.str_to_date(@start_date_str)  
set @end_date=dbo.str_to_date(@end_date_str)  

 
 declare @PeriodeName varchar(255)--,
-- @stock_date datetime,
-- @closing_with_stock_opname bit ,
-- @closing_stock_opname_id bigint
      
--declare @end_date_new_mesin date
--SELECT top(1) @end_date_new_mesin= end_date
--FROM dt_ms_cost_final_avg
--WHERE (ms_cost_final_avg_id = 0)
 

--if @start_date>=@end_date_new_mesin
--begin
--SELECT top 1 @stock_date=doc_date
-- FROM vw_ms_sto_header
-- WHERE (status_adj_sto_id = 1) AND (doc_date = @end_date)
--end
--else
--begin
-- select top 1
-- @stock_date= cast(date_stock_opname as date) from dbo.tbl_stock_opname
-- where date_stock_opname < @start_date and typebahan = 'RM'
-- order by date_stock_opname desc
--end


--if @stock_date is not null
--set @closing_with_stock_opname=1
--else
--set @closing_with_stock_opname=0
 

set  @PeriodeName=   isnull('PERIODE ' +  dbo.dateToIndStr( @start_date) +' S.D ' + dbo.dateToIndStr( @end_date),'') 
 
declare @pakai_sto_terakhir bit
 
set @pakai_sto_terakhir=1; 
 


/* ifield iname iBolehSearch= iSearchtype= 1= STRING ,2=ANGKA , 3=DATETIME, iSearchByPassLike = 1 ya , 0 no SELECT dt_barang.barang_code AS rID, dt_barang.barang_name AS r1, dt_satuan.satuan_name AS r2, dt_mutasi_barang.saldo_awal AS r3, dt_mutasi_barang.pemasukan AS r4, dt_mutasi_barang.pengeluaran AS r5, dt_mutasi_barang.penyesuaian AS r6, (dt_mutasi_barang.saldo_awal + dt_mutasi_barang.pemasukan ) -(dt_mutasi_barang.pengeluaran + dt_mutasi_barang.penyesuaian) AS r7, '-' AS r8, '-' AS r9, '-' AS r10 FROM dt_mutasi_barang INNER JOIN dt_barang ON dt_mutasi_barang.barang_id = dt_barang.barang_id INNER JOIN dt_satuan ON dt_barang.satuan_code = dt_satuan.satuan_code WHERE (dt_mutasi_barang.periode_id = @periode_id) AND (dt_barang.barang_category_id = 1 OR dt_barang.barang_category_id = 2) */
declare @TABELDATA varchar(max)
set @XMLMAPING =
' '
  
declare @barang_hide table (barang_id bigint primary key)



set @TABELDATA=
' FROM fn_rpt_mutasi_mesin(@start_date,@end_date) ';

declare  
 
  @OutSortBy varchar(255),
  @rumusWhere varchar(max) ,
     @search xml,
     @fixWhere varchar(2000)

  if @ms_type_id = -1
   begin
    set @fixWhere = '';
   end
  else
   begin
    set @fixWhere ='(ms_type_id=' + cast(@ms_type_id as varchar(255))+') AND ';
   end

EXEC  [dbo].[MyRUMUSWhereAndSortView]
  @PARAMETER =@PARAMETER,
  @XMLMAPING = @XMLMAPING,
  @fixWhere = @fixWhere,
  @Sort =@sort,
  @desc = @desc,
  @OutSortBy = @OutSortBy OUTPUT,
  @rumusWhere = @rumusWhere OUTPUT,
  @search = @search OUTPUT;
  
DECLARE @SQLString nvarchar(MAX);
DECLARE @ParmDefinition nvarchar(MAX);

--OLAH DETAIL +++++++++++++++++++++
declare @jmlrow bigint;
SET @SQLString = 
N' set @jmlrow=( SELECT count(*) ' 
+@TABELDATA
+ @rumusWhere + ' )' ;
SET @ParmDefinition = N'@start_date date, @end_date date, @jmlrow bigint OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@start_date=@start_date,
@end_date=@end_date,
@jmlrow = @jmlrow OUTPUT 
  
declare @detail xml
declare @totalpages bigint


declare @SISAROWS bigint
set @SISAROWS =@jmlrow % @rowspage

 if @SISAROWS=0
  set @totalpages= @jmlrow/@rowspage
 else
  set @totalpages=  ((@jmlrow-@SISAROWS)/@rowspage) + 1
 
 
 
   declare @PT_NAME varchar(max)
   SELECT     TOP (1) @PT_NAME = isnull(CONVERT(varchar(MAX),DecryptByPassphrase('Ap4TanyaT4nya MauT4uAj4 4h'    , set_2)),'DEMO ONLY')  
  FROM         dt_app_set

  set  @detail=(
    SELECT  top 1   
   @sort as 'sort' , 
   @desc as 'desc' , 
   @rowspage as 'rowspage' , 
   @jmlrow as 'rows' , 
   @totalpages as 'pages' ,
   @PeriodeName as 'periode_name',
   @PT_NAME as 'nama_pt',
   @xlsreadonly as 'xls_readonly',
   --dbo.date_to_str(@stock_date) as 'tanggal_stok',
   CONVERT(varchar(255),NEWID()) as 'report_ID'
   FROM         APP_SETTING

   for xml path('detail') )
 

--OLAH DATA +++++++++++++++++++++


SET @SQLString = 
N' set @raw=( SELECT rn,rID,r1,r2,r3,r4,r5,r6,r7,r8,r9,r10,r11,r12 FROM( SELECT ' + 
case when @mode =1 then 
'ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + ') as rn, '  
else 
'ROW_NUMBER() OVER(ORDER BY custom_no) as rn, '  
end + 
' custom_no AS rID , base_no AS r1 , barang_code AS r2 , barang_name AS r3 , upper(satuan_code) AS r4 , saldo_awal AS r5 , pemasukan AS r6 , pengeluaran AS r7 , penyesuaian AS r8 , saldo_akhir AS r9 , stock_opname AS r10 , selisih AS r11 , keterangan AS r12 ' 
+ @TABELDATA + @rumusWhere  + ' ) XDATA '

if (@mode =1)
begin
set @SQLString = @SQLString +      'WHERE rN BETWEEN (' + cast((@page) as varchar(max)) +' -1) * ' + cast(@ROWSPAGE  as varchar(max)) + 
' + 1 AND '+ CAST(@page * @ROWSPAGE as varchar(max))   + ' for xml PATH(''xrow'')) '
end
else
begin
set @SQLString = @SQLString +      ' for xml PATH(''xrow'')) '
end
--print @SQLString

SET @ParmDefinition = N'@start_date date, @end_date date, @raw XML OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@start_date=@start_date,
@end_date=@end_date,
@raw = @raw OUTPUT 
 
f:
 
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))

 select  CAST( (select  @XmlSts,@detail,@raw   for xml path ('result')
)  as XML)  as dt;

--go
--exec dbo.RPT_DYNAMIC_MUTASI_MESIN_TEST
--'12::1dbo.RPT_DYNAMIC_MUTASI_MESIN_TEST1202::12xlsx2020062720200727C:\Windows\TEMP\11'


GO