dbo.MG_MS_ADJUSTMEN_SOURCE

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.MG_MS_ADJUSTMEN_SOURCE
@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(255) 
 ,@rowformat varchar(255)
 ,@XMLMAPING xml
 ,@TABELDATA varchar(max)
 ,@OutSortBy varchar(255)
 ,@rumusWhere varchar(max)
 ,@search xml
 ,@SQLString nvarchar(MAX)
 ,@fixWhere nvarchar(2000)
 ,@ParmDefinition nvarchar(MAX)
 ,@detail xml
 ,@totalpages bigint
 ,@rowJSON varchar(max);
set @xStsnoheader='01';
set @xStsno='00';
set @xStsdes='Success';
declare @header_id bigint
SELECT
  @header_id=isnull(tempTable.xItem.value('header_id[1]', 'bigint'),-1)
 ,@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')
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)
declare @doc_date date 
SELECT  top 1  @doc_date=    doc_date
FROM            vw_ms_adjustment_header
WHERE        (ms_adj_sto_id = @header_id)
----#####START SOURCE#####
----SELECT ms_in_item_detail_id, part_no, part_name, mode_source_2, mode_source_name_2, bc_no, bc_date, uom, quantity_pakai, quantity_sisa, receive_no, receive_date, car, ms_item_custom_no, ms_item_base_no, status_id, status_name, SERI_BARANG, ori_ms_in_item_detail_id, id_source_header, ms_item_id, ms_type_id
----FROM vw_ms_item_in_detail;
----#####END SOURCE#####

set @XMLMAPING =
' ';
set @TABELDATA=
' FROM vw_ms_item_in_detail LEFT OUTER JOIN ( SELECT id_source , ms_in_item_detail_id, quantity_pakai, info FROM dt_ms_item_in_detail WHERE (id_source_header = ' + cast(@header_id as varchar(255)) +
') AND (mode_source = 3) ) AS used_table ON vw_ms_item_in_detail.ms_in_item_detail_id = used_table.id_source ';

set @fixWhere ='(vw_ms_item_in_detail.receive_date <=@doc_date ) AND (vw_ms_item_in_detail.status_id=1) AND ';
EXEC [dbo].[MyRUMUSWhereAndSortView]
 @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'@doc_date date ,@header_id bigint,@jmlrow bigint OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition
,@doc_date=@doc_date
 ,@header_id=@header_id
 ,@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 , r4 , r5 , r6 , r7 , r8 , r9 , r10 , r11 , r12 , r13 , r14 , r15 , r16 , r17 , r18 , r19 , r20 , r21 ,r_s , r_u , r_uq, r_ua, r_uid FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , vw_ms_item_in_detail.ms_in_item_detail_id AS rID , vw_ms_item_in_detail.part_no AS r1 , vw_ms_item_in_detail.part_name AS r2 , vw_ms_item_in_detail.mode_source_2 AS r3 , vw_ms_item_in_detail.mode_source_name_2 AS r4 , vw_ms_item_in_detail.bc_no AS r5 , dbo.date_to_str(vw_ms_item_in_detail.bc_date ) AS r6 , vw_ms_item_in_detail.uom AS r7 , cast(vw_ms_item_in_detail.quantity_pakai AS decimal(38,4)) AS r8 , cast(vw_ms_item_in_detail.quantity_sisa AS decimal(38,4)) AS r9 , vw_ms_item_in_detail.receive_no AS r10 , dbo.date_to_str(vw_ms_item_in_detail.receive_date ) AS r11 , vw_ms_item_in_detail.car AS r12 , vw_ms_item_in_detail.ms_item_custom_no AS r13 , vw_ms_item_in_detail.ms_item_base_no AS r14 , vw_ms_item_in_detail.status_id AS r15 , vw_ms_item_in_detail.status_name AS r16 , vw_ms_item_in_detail.SERI_BARANG AS r17 , vw_ms_item_in_detail.ori_ms_in_item_detail_id AS r18 , vw_ms_item_in_detail.id_source_header AS r19 , vw_ms_item_in_detail.ms_item_id AS r20 , vw_ms_item_in_detail.ms_type_id AS r21 , 1 as r_s , cast(case when used_table.ms_in_item_detail_id is null then 0 else 1 end as bit) as r_u , cast(isnull(used_table.quantity_pakai,0) AS decimal(38,4)) as r_uq , cast(vw_ms_item_in_detail.quantity_sisa + isnull(used_table.quantity_pakai,0) AS decimal(38,4)) as r_ua , isnull(used_table.ms_in_item_detail_id,-1) as r_uid '
+ @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'@doc_date date ,@header_id bigint, @raw XML OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@doc_date=@doc_date,
@header_id=@header_id,
@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) + ''","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) + ''","r_s":"'' + dbo.JSONEscaped(r_s) + ''","r_u":"'' + dbo.JSONEscaped(r_u) + ''","r_uq":"'' + dbo.JSONEscaped(r_uq) + ''","r_ua":"'' + dbo.JSONEscaped(r_ua) + ''","r_uid":"'' + dbo.JSONEscaped(r_uid) + ''","r_uinfo":"'' + dbo.JSONEscaped(r_uinfo) + ''"}'' FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , vw_ms_item_in_detail.ms_in_item_detail_id AS rID , vw_ms_item_in_detail.part_no AS r1 , vw_ms_item_in_detail.part_name AS r2 , vw_ms_item_in_detail.mode_source_2 AS r3 , vw_ms_item_in_detail.mode_source_name_2 AS r4 , vw_ms_item_in_detail.bc_no AS r5 , dbo.date_to_str(vw_ms_item_in_detail.bc_date ) AS r6 , vw_ms_item_in_detail.uom AS r7 , cast(vw_ms_item_in_detail.quantity_pakai AS decimal(38,4)) AS r8 , cast(vw_ms_item_in_detail.quantity_sisa AS decimal(38,4)) AS r9 , vw_ms_item_in_detail.receive_no AS r10 , dbo.date_to_str(vw_ms_item_in_detail.receive_date ) AS r11 , vw_ms_item_in_detail.car AS r12 , vw_ms_item_in_detail.ms_item_custom_no AS r13 , vw_ms_item_in_detail.ms_item_base_no AS r14 , vw_ms_item_in_detail.status_id AS r15 , vw_ms_item_in_detail.status_name AS r16 , vw_ms_item_in_detail.SERI_BARANG AS r17 , vw_ms_item_in_detail.ori_ms_in_item_detail_id AS r18 , vw_ms_item_in_detail.id_source_header AS r19 , vw_ms_item_in_detail.ms_item_id AS r20 , vw_ms_item_in_detail.ms_type_id AS r21 , 1 as r_s , cast(case when used_table.ms_in_item_detail_id is null then 0 else 1 end as bit) as r_u , cast(isnull(used_table.quantity_pakai,0) AS decimal(38,4)) as r_uq , cast(vw_ms_item_in_detail.quantity_sisa + case when isnull(used_table.quantity_pakai,0) <0 then abs(isnull(used_table.quantity_pakai,0)) else 0 end AS decimal(38,4)) as r_ua , isnull(used_table.ms_in_item_detail_id,-1) as r_uid , isnull(used_table.info,'''') as r_uinfo '
 + @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'@doc_date date ,@header_id bigint, @rowJSON varchar(max) OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@doc_date=@doc_date,
@header_id=@header_id,
@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