dbo.RPT_DYNAMIC_BARANG_PER_DOKUMENT_IN_AW_2340

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE procedure dbo.RPT_DYNAMIC_BARANG_PER_DOKUMENT_IN_AW_2340
@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,
   @mode int,
  @format varchar(255),
  @source varchar(255),
  @bc_type varchar(255)

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=dbo.str_to_date(tempTable.xItem.value('start_date[1]', 'varchar(255)') ) ,
 @end_date=dbo.str_to_date(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) ,
  @source=isnull(tempTable.xItem.value('source[1]', 'varchar(255)'),'')  ,
  @bc_type=isnull(tempTable.xItem.value('bc_type[1]', 'varchar(255)'),'') 
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)
declare @XMLMAPING xml

 ------ .'' 20130128
 ------ .'' 20130128
 --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
 --3110 Report Pabean IN Invoice False True True NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
 --3111 Report Pabean OUT Invoice False True True NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
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=3011
   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


declare @PeriodeName varchar(255)
 
set @PeriodeName=  'PERIODE ' +  dbo.dateToIndStr(@start_date) +' S.D ' + dbo.dateToIndStr( @end_date)
 
declare @maxdec int

set @maxdec=(SELECT        MAX(set_decimal.detail_total) AS Expr1
FROM            set_decimal INNER JOIN
                         enum_bc_type ON set_decimal.bc_type_id = enum_bc_type.bc_type_id
WHERE        (enum_bc_type.x = 1))

if @maxdec is null
set @maxdec=4
 
/* ifield iname iBolehSearch= iSearchtype= 1= STRING ,2=ANGKA , 3=DATETIME, iSearchByPassLike = 1 ya , 0 no SELECT doc_in_barang_detail_id AS rID, bc_type_name AS r1, doc_no AS r2, doc_date AS r3, recieve_number AS r4, recieve_date AS r5, supplier_receiver_name AS r6, barang_code AS r7, barang_name AS r8, satuan_name AS r9, doc_qty AS r10, doc_in_from_id AS r11, status_doc_in_id AS r12, bc_type_id AS r13, valuta_code AS r14, total_cif AS r15 FROM vw_rpt_doc_in_bc_by_document */
declare @bisaFIlter bit
set @bisaFIlter=1
if (@mode =2)
begin
set @additional_id=6501 --Print Out Per Type BC
set @additional_value= dbo.additional_info(@additional_id)
  if    @additional_value =0
  begin
  set @bisaFIlter=0  
   end
  else
  begin
  set @bisaFIlter=1
   end
end
  
declare @TABELDATA varchar(max)
set @XMLMAPING =
' + case when @mode =2 then  cast(@bisaFIlter as varchar(255)) else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="bc_type_name" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="doc_no" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="3" iSearchByPassLike ="0" iname="doc_date" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="recieve_number" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="3" iSearchByPassLike ="0" iname="recieve_date" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="supplier_receiver_name" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="barang_code" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="barang_name" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="doc_satuan_code" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="2" iSearchByPassLike ="0" iname="doc_qty" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="valuta_code" > + case when @mode =2 then  '0' else '1' end  + '" iSearchtype="2" iSearchByPassLike ="0" iname="cast(total_cif as decimal(18,' + cast( @maxdec as varchar(255))+ 
'))" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="invoice_no" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="3" iSearchByPassLike ="0" iname="invoice_date" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="NOMOR_AJU_6" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="KEMASAN" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="BRUTO" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="NETTO" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="KODE_PEL_BONGKAR" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="BL" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="KONTAINER" > + case when @mode =2 then  '0' else '1' end  + 
'" iSearchtype="1" iSearchByPassLike ="0" iname="NAMA_PENGANGKUT" > '

 set @TABELDATA=case when @source='wh' then 
' FROM gab_rpt_incoming_wh(@start_date,@end_date,@bc_type) 'when @source='aw' then 
' FROM gab_rpt_incoming_aw(@start_date,@end_date,@bc_type) 'when @source='aw2340' then 
' FROM gab_rpt_mutasi_aw2340(@start_date,@end_date,@bc_type) 'else  
' FROM gab_rpt_incoming_all(@start_date,@end_date,@bc_type) '
  end 

declare  
   
  @OutSortBy varchar(255),
  @rumusWhere varchar(max) ,
     @search xml,
     @fixWhere varchar(2000)
     set @fixWhere=''
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);
print @rumusWhere
--OLAH DETAIL +++++++++++++++++++++
declare @jmlrow bigint;
SET @SQLString = 
N' set @jmlrow=( SELECT count(*) ' 
+@TABELDATA
+ @rumusWhere + ' )' ;
SET @ParmDefinition = N'@start_date date, @end_date date,@bc_type varchar(255) ,@jmlrow bigint OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@start_date=@start_date,
@end_date=@end_date,
@bc_type=@bc_type,
@jmlrow = @jmlrow OUTPUT 
  
declare @detail xml
declare @totalpages bigint


if (@mode =1)
begin

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

 if @SISAROWS=0
  set @totalpages= @jmlrow/@rowspage
 else
  set @totalpages=  ((@jmlrow-@SISAROWS)/@rowspage) + 1

end
else
begin
set @totalpages=1
end

   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',
   @maxdec as 'max_decimal_digit_price',
   @xlsreadonly as 'xls_readonly',
   CONVERT(varchar(255),NEWID()) as 'report_ID',
   dbo.additional_info(3110) as 'ada_invoice',
   4 as 'max_decimal_digit_price'
   FROM         APP_SETTING
   for xml path('detail') )
 

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

SET @SQLString = 
N' set @raw=( SELECT rn,r1,r2,r3,r4,r5,r6,r7,r8,r9,r10,r11,r12,r13,r14,r15,r16,r17,r18,r19,r20,r21,r22 FROM( SELECT ' + 
case when @mode =2 then 
'ROW_NUMBER() OVER(ORDER BY recieve_date,bc_type_name,doc_date,doc_no) as rn, '
else 
'ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + ') as rn, '
end + 
' bc_type_name AS r1, doc_no AS r2, dbo.dateToIndStr(doc_date) AS r3, recieve_number AS r4, isnull(dbo.dateToIndStr(recieve_date),'''') AS r5, isnull(supplier_receiver_name,'''') AS r6, barang_code AS r7, barang_name AS r8, isnull(upper(doc_satuan_code),'''') AS r9, isnull(doc_qty,0) AS r10, isnull(upper(valuta_code),'''') AS r11, cast(total_cif as decimal(24,' + cast( @maxdec as varchar(255)) + 
')) as r12 , invoice_no AS r13, dbo.dateToIndStr(invoice_date) AS r14 , NOMOR_AJU_6 AS r15, KEMASAN AS r16, BRUTO AS r17, NETTO AS r18, KODE_PEL_BONGKAR AS r19, BL AS r20, KONTAINER AS r21, NAMA_PENGANGKUT AS r22 ' 
+ @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 
SET @ParmDefinition = N'@start_date date, @end_date date,@bc_type varchar(255) , @raw XML OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@start_date=@start_date,
@end_date=@end_date,
@bc_type=@bc_type,
@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