dbo.MG_MS_BC_OUT

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.MG_MS_BC_OUT
@PARAMETER xml
---XencX---with encryption
as
SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED

SET NOCOUNT ON
declare @xStsnoheader varchar(2)
 ,@xStsno varchar(2)
 ,@xStsdes varchar(max)
 ,@XmlSts xml
 ,@raw xml
 ,@rowspage int
 ,@page int
 ,@desc int
 ,@sort varchar(max) 
 ,@rowformat varchar(max)
 ,@XMLMAPING xml
 ,@TABELDATA varchar(max)
 ,@OutSortBy varchar(max)
 ,@rumusWhere varchar(max)
 ,@search xml
 ,@SQLString nvarchar(MAX)
 ,@fixWhere nvarchar(max)
 ,@ParmDefinition nvarchar(MAX)
 ,@detail xml
 ,@totalpages bigint
 ,@rowJSON varchar(max);
set @xStsnoheader='01';
set @xStsno='00';
set @xStsdes='Success';

declare @bc_prefix 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')
 ,@rowformat=isnull(tempTable.xItem.value('@rowformat', 'varchar(255)'),'xml')
 ,@bc_prefix=isnull(tempTable.xItem.value('bc_prefix[1]', 'varchar(255)'),'') 
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)

----#####START SOURCE#####
----SELECT ID_HEADER, status_tpb_out_id, status_tpb_out_name, mode_source_id, mode_source_name, is_get, KODE_DOKUMEN_PABEAN, NOMOR_AJU, PJT, TANGGAL_DAFTAR, NOMOR_DAFTAR, KODE_STATUS, URAIAN_STATUS, KODE_TUJUAN_IN_OUT, URAINAN_TUJUAN_IN_OUT, MODULE_ID_PENGIRIM, MODULE_NAMA_PENGIRIM, MODULE_KODE_ID_PENGIRIM, MODULE_ALAMAT_PENGIRIM, MODULE_KODE_NEGARA_PENGIRIM, MODULE_NAMA_NEGARA_PENGIRIM, id_type, INTERNAL_ID_PENGIRIM, INTERNAL_KODE_PENGIRIM, INTERNAL_NAMA_PENGIRIM, INTERNAL_ALAMAT_PENGIRIM, INTERNAL_KODE_NEGARA_PENGIRIM, INTERNAL_NAMA_NEGARA_PENGIRIM, api_type_id, api_no, skep_no, skep_date, KODE_VALUTA, NDPBM, KODE_HARGA, HARGA_TOTAL, KODE_ASURANSI, ASURANSI, BIAYA_TAMBAHAN, DISKON, FREIGHT, BRUTO, NETTO, JUMLAH_BARANG, JUMLAH_KEMASAN, JUMLAH_KONTAINER, KODE_KANTOR_BONGKAR, KODE_KANTOR_BONGKAR_NAME, KODE_PEL_BONGKAR, KODE_PEL_BONGKAR_NAME, surat_jalan_no, surat_jalan_date, WAKTU_BONGKAR, WAKTU_STUFFING, status_info, status_date, status_by, create_date, create_by, modify_date, modify_by, ID_HEADER_ORI
----FROM vw_ms_header_BC_OUT;
----#####END SOURCE#####

set @XMLMAPING =
' ';
 
set @TABELDATA=
' FROM vw_ms_header_BC_OUT ';
set @fixWhere ='(KODE_DOKUMEN_PABEAN=''' + @bc_prefix +''') AND '; 
EXEC [dbo].[MyRUMUSWhereAndSortView10]
 @PARAMETER =@PARAMETER
 ,@XMLMAPING = @XMLMAPING
 ,@fixWhere =@fixWhere
 ,@Sort =@sort
 ,@desc = @desc
 ,@OutSortBy = @OutSortBy OUTPUT
 ,@rumusWhere = @rumusWhere OUTPUT
 ,@search = @search OUTPUT;
--OLAH DETAIL +++++++++++++++++++++
declare @jmlrow bigint;
SET @SQLString = N' set @jmlrow=( SELECT count(*)' +@TABELDATA+ @rumusWhere + ' )' ;
SET @ParmDefinition = N'@jmlrow bigint OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition
 ,@jmlrow = @jmlrow OUTPUT 

declare @SISAROWS bigint
set @SISAROWS =@jmlrow % @rowspage
if @SISAROWS=0
 set @totalpages= @jmlrow/@rowspage
Else
 set @totalpages=  ((@jmlrow-@SISAROWS)/@rowspage) + 1;

set  @detail=(
 select 
 @sort as 'sort'
 ,@desc as 'desc'
 ,@rowspage as 'rowspage'
 ,@jmlrow as 'rows'
 ,@totalpages as 'pages'
 ,@search   as 'search'
 for xml path('detail') )

if Upper(@rowformat)='XML'
begin
SET @SQLString = 
N' set @raw=( SELECT rn , rID , r1 , r2 , r3 , r3t , r4 , r5 , r6 , r7 , r8 , r9 , r10 , r11 , r12 , r13 , r14 , r15 , r16 , r17 , r18 , r19 , r20 , r21 ,r21id , r22 , r23 , r24 , r25 , r26 , r27 , r28 , r29 , r30 , r31 , r32 , r33 , r34 , r35 , r36 , r37 , r38 , r39 , r40 , r41 , r42 , r43 , r44 , r45 , r46 , r47 , r48 , r49 , r50 , r51 , r52 , r53 , r54 , r55 , r56 , r57 , r58 , r59 , r60 , r61 FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , ID_HEADER AS rID , status_tpb_out_id AS r1 , status_tpb_out_name AS r2 , mode_source_id AS r3 , type_source_id as r3t , mode_source_name AS r4 , is_get AS r5 , KODE_DOKUMEN_PABEAN AS r6 , NOMOR_AJU AS r7 , PJT AS r8 , dbo.date_to_str(TANGGAL_DAFTAR ) AS r9 , NOMOR_DAFTAR AS r10 , KODE_STATUS AS r11 , URAIAN_STATUS AS r12 , KODE_TUJUAN_IN_OUT AS r13 , URAINAN_TUJUAN_IN_OUT AS r14 , MODULE_ID_PENGIRIM AS r15 , MODULE_NAMA_PENGIRIM AS r16 , MODULE_KODE_ID_PENGIRIM AS r17 , MODULE_ALAMAT_PENGIRIM AS r18 , MODULE_KODE_NEGARA_PENGIRIM AS r19 , MODULE_NAMA_NEGARA_PENGIRIM AS r20 , id_type AS r21 , mapping_supplier_id AS r21id , INTERNAL_ID_PENGIRIM AS r22 , INTERNAL_KODE_PENGIRIM AS r23 , INTERNAL_NAMA_PENGIRIM AS r24 , INTERNAL_ALAMAT_PENGIRIM AS r25 , INTERNAL_KODE_NEGARA_PENGIRIM AS r26 , INTERNAL_NAMA_NEGARA_PENGIRIM AS r27 , api_type_id AS r28 , api_no AS r29 , skep_no AS r30 , dbo.date_to_str(skep_date ) AS r31 , KODE_VALUTA AS r32 , cast(NDPBM AS decimal(18,4)) AS r33 , KODE_HARGA AS r34 , cast(HARGA_TOTAL AS decimal(18,4)) AS r35 , KODE_ASURANSI AS r36 , cast(ASURANSI AS decimal(18,4)) AS r37 , cast(BIAYA_TAMBAHAN AS decimal(18,4)) AS r38 , cast(DISKON AS decimal(18,4)) AS r39 , cast(FREIGHT AS decimal(18,4)) AS r40 , cast(BRUTO AS decimal(18,4)) AS r41 , cast(NETTO AS decimal(18,4)) AS r42 , JUMLAH_BARANG AS r43 , JUMLAH_KEMASAN AS r44 , JUMLAH_KONTAINER AS r45 , KODE_KANTOR_BONGKAR AS r46 , KODE_KANTOR_BONGKAR_NAME AS r47 , KODE_PEL_BONGKAR AS r48 , KODE_PEL_BONGKAR_NAME AS r49 , surat_jalan_no AS r50 , dbo.date_to_str(surat_jalan_date ) AS r51 , WAKTU_BONGKAR AS r52 , WAKTU_STUFFING AS r53 , status_info AS r54 , dbo.date_to_str(status_date ) AS r55 , status_by AS r56 , dbo.date_to_str(create_date ) AS r57 , create_by AS r58 , dbo.date_to_str(modify_date ) AS r59 , modify_by AS r60 , ID_HEADER_ORI AS r61 '
+ @TABELDATA + @rumusWhere  + ' ) XDATA '
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'')) '

SET @ParmDefinition = N'@raw XML OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@raw = @raw OUTPUT 

end
else if Upper(@rowformat)='JSON'
begin
SET @SQLString = 
N' set @rowJSON=(''['' + STUFF(( SELECT '',{"rn":"'' + dbo.JSONEscaped(rn) + ''","rID":"'' + dbo.JSONEscaped(rID) + ''","r1":"'' + dbo.JSONEscaped(r1) + ''","r2":"'' + dbo.JSONEscaped(r2) + ''","r3":"'' + dbo.JSONEscaped(r3) + ''","r3t":"'' + dbo.JSONEscaped(r3t) + ''","r4":"'' + dbo.JSONEscaped(r4) + ''","r5":"'' + dbo.JSONEscaped(r5) + ''","r6":"'' + dbo.JSONEscaped(r6) + ''","r7":"'' + dbo.JSONEscaped(r7) + ''","r8":"'' + dbo.JSONEscaped(r8) + ''","r9":"'' + dbo.JSONEscaped(r9) + ''","r10":"'' + dbo.JSONEscaped(r10) + ''","r11":"'' + dbo.JSONEscaped(r11) + ''","r12":"'' + dbo.JSONEscaped(r12) + ''","r13":"'' + dbo.JSONEscaped(r13) + ''","r14":"'' + dbo.JSONEscaped(r14) + ''","r15":"'' + dbo.JSONEscaped(r15) + ''","r16":"'' + dbo.JSONEscaped(r16) + ''","r17":"'' + dbo.JSONEscaped(r17) + ''","r18":"'' + dbo.JSONEscaped(r18) + ''","r19":"'' + dbo.JSONEscaped(r19) + ''","r20":"'' + dbo.JSONEscaped(r20) + ''","r21":"'' + dbo.JSONEscaped(r21) + ''","r21id":"'' + dbo.JSONEscaped(r21id) + ''","r22":"'' + dbo.JSONEscaped(r22) + ''","r23":"'' + dbo.JSONEscaped(r23) + ''","r24":"'' + dbo.JSONEscaped(r24) + ''","r25":"'' + dbo.JSONEscaped(r25) + ''","r26":"'' + dbo.JSONEscaped(r26) + ''","r27":"'' + dbo.JSONEscaped(r27) + ''","r28":"'' + dbo.JSONEscaped(r28) + ''","r29":"'' + dbo.JSONEscaped(r29) + ''","r30":"'' + dbo.JSONEscaped(r30) + ''","r31":"'' + dbo.JSONEscaped(r31) + ''","r32":"'' + dbo.JSONEscaped(r32) + ''","r33":"'' + dbo.JSONEscaped(r33) + ''","r34":"'' + dbo.JSONEscaped(r34) + ''","r35":"'' + dbo.JSONEscaped(r35) + ''","r36":"'' + dbo.JSONEscaped(r36) + ''","r37":"'' + dbo.JSONEscaped(r37) + ''","r38":"'' + dbo.JSONEscaped(r38) + ''","r39":"'' + dbo.JSONEscaped(r39) + ''","r40":"'' + dbo.JSONEscaped(r40) + ''","r41":"'' + dbo.JSONEscaped(r41) + ''","r42":"'' + dbo.JSONEscaped(r42) + ''","r43":"'' + dbo.JSONEscaped(r43) + ''","r44":"'' + dbo.JSONEscaped(r44) + ''","r45":"'' + dbo.JSONEscaped(r45) + ''","r46":"'' + dbo.JSONEscaped(r46) + ''","r47":"'' + dbo.JSONEscaped(r47) + ''","r48":"'' + dbo.JSONEscaped(r48) + ''","r49":"'' + dbo.JSONEscaped(r49) + ''","r50":"'' + dbo.JSONEscaped(r50) + ''","r51":"'' + dbo.JSONEscaped(r51) + ''","r52":"'' + dbo.JSONEscaped(r52) + ''","r53":"'' + dbo.JSONEscaped(r53) + ''","r54":"'' + dbo.JSONEscaped(r54) + ''","r55":"'' + dbo.JSONEscaped(r55) + ''","r56":"'' + dbo.JSONEscaped(r56) + ''","r57":"'' + dbo.JSONEscaped(r57) + ''","r58":"'' + dbo.JSONEscaped(r58) + ''","r59":"'' + dbo.JSONEscaped(r59) + ''","r60":"'' + dbo.JSONEscaped(r60) + ''","r61":"'' + dbo.JSONEscaped(r61) + ''"}'' FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , ID_HEADER AS rID , status_tpb_out_id AS r1 , status_tpb_out_name AS r2 , mode_source_id AS r3 , type_source_id as r3t , mode_source_name AS r4 , is_get AS r5 , KODE_DOKUMEN_PABEAN AS r6 , NOMOR_AJU AS r7 , PJT AS r8 , dbo.date_to_str(TANGGAL_DAFTAR ) AS r9 , NOMOR_DAFTAR AS r10 , KODE_STATUS AS r11 , URAIAN_STATUS AS r12 , KODE_TUJUAN_IN_OUT AS r13 , URAINAN_TUJUAN_IN_OUT AS r14 , MODULE_ID_PENGIRIM AS r15 , MODULE_NAMA_PENGIRIM AS r16 , MODULE_KODE_ID_PENGIRIM AS r17 , MODULE_ALAMAT_PENGIRIM AS r18 , MODULE_KODE_NEGARA_PENGIRIM AS r19 , MODULE_NAMA_NEGARA_PENGIRIM AS r20 , id_type AS r21 , mapping_supplier_id AS r21id , INTERNAL_ID_PENGIRIM AS r22 , INTERNAL_KODE_PENGIRIM AS r23 , INTERNAL_NAMA_PENGIRIM AS r24 , INTERNAL_ALAMAT_PENGIRIM AS r25 , INTERNAL_KODE_NEGARA_PENGIRIM AS r26 , INTERNAL_NAMA_NEGARA_PENGIRIM AS r27 , api_type_id AS r28 , api_no AS r29 , skep_no AS r30 , dbo.date_to_str(skep_date ) AS r31 , KODE_VALUTA AS r32 , cast(NDPBM AS decimal(18,4)) AS r33 , KODE_HARGA AS r34 , cast(HARGA_TOTAL AS decimal(18,2)) AS r35 , KODE_ASURANSI AS r36 , cast(ASURANSI AS decimal(18,2)) AS r37 , cast(BIAYA_TAMBAHAN AS decimal(18,2)) AS r38 , cast(DISKON AS decimal(18,2)) AS r39 , cast(FREIGHT AS decimal(18,2)) AS r40 , cast(BRUTO AS decimal(18,2)) AS r41 , cast(NETTO AS decimal(18,2)) AS r42 , JUMLAH_BARANG AS r43 , JUMLAH_KEMASAN AS r44 , JUMLAH_KONTAINER AS r45 , KODE_KANTOR_BONGKAR AS r46 , KODE_KANTOR_BONGKAR_NAME AS r47 , KODE_PEL_BONGKAR AS r48 , KODE_PEL_BONGKAR_NAME AS r49 , surat_jalan_no AS r50 , dbo.date_to_str(surat_jalan_date ) AS r51 , WAKTU_BONGKAR AS r52 , WAKTU_STUFFING AS r53 , status_info AS r54 , dbo.date_to_str2(status_date ) AS r55 , status_by AS r56 , dbo.date_to_str2(create_date ) AS r57 , create_by AS r58 , dbo.date_to_str2(modify_date ) AS r59 , modify_by AS r60 , ID_HEADER_ORI AS r61 '
 + @TABELDATA + @rumusWhere  + ' ) XDATA '
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(''''), type ).value(''.'', ''varchar(max)''), 1, 1, '''') + '']'')';

SET @ParmDefinition = N'@rowJSON varchar(max) OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@rowJSON = @rowJSON OUTPUT 

set  @rowJSON ='{"page":"'+  cast(@page as varchar(255))  + '","total":"' +  cast(@totalpages as varchar(255))  + '","records":"' + cast(@jmlrow as varchar(255)) +'","rows":' + isnull(@rowJSON,'{}') + '}'

end
f:
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
SET NOCOUNT OFF
select  CAST( (select  @XmlSts,@detail,@raw , @rowJSON as jsondata for xml path ('result'))  as XML)  as dt

-- GO
--exec MG_MS_BC_OUT
--'12dbo.MG_MS_BC_OUT2::123'
GO