dbo.MG_MS_OUT_CHANGE_PART_LIST_SOURCE_AVAILABLE

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.MG_MS_OUT_CHANGE_PART_LIST_SOURCE_AVAILABLE
@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 @ms_part_list_change_part_detail_id bigint

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')
 ,@ms_part_list_change_part_detail_id=isnull(tempTable.xItem.value('ms_part_list_change_part_detail_id[1]', 'int'),-1)
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)

----#####START SOURCE#####
----SELECT ms_in_item_detail_id, mode_source, mode_source_name, mode_source_2, mode_source_name_2, car, bc_no, bc_date, receive_no, receive_date, status_id, quantity_pakai, quantity_sisa, qty_out, qty_plan, car_line_out, memo_out, car_line_plan, memo_plan, ms_type_id, ms_item_custom_no, ms_item_base_no, part_no, part_name, uom, ori_ms_in_item_detail_id, id_source_header
----FROM vw_ms_item_in_detail;
----#####END SOURCE#####
declare @check_ms_part_list_production_detail_id bigint
declare @check_ms_item_id bigint
declare @check_doc_date date
declare @total_quantity decimal(38,4)
SELECT top(1)        
  @check_ms_part_list_production_detail_id=dt_ms_part_list_change_part_detail.ms_part_list_change_part_detail_id
, @check_ms_item_id=dt_ms_part_list_change_part_detail.ms_item_id
, @check_doc_date=dt_ms_part_list_change_part_header.ms_part_list_change_part_date
, @total_quantity= dt_ms_part_list_change_part_detail.quantity
FROM dt_ms_part_list_change_part_detail INNER JOIN
                         dt_ms_part_list_change_part_header ON dt_ms_part_list_change_part_detail.ms_part_list_change_part_id = dt_ms_part_list_change_part_header.ms_part_list_change_part_id
WHERE        (dt_ms_part_list_change_part_detail.ms_part_list_change_part_detail_id = @ms_part_list_change_part_detail_id)

declare @total_avialable decimal(38,4) 

declare @total_supply decimal(38,4) = 0 
, @total_new_part decimal(38,4) = 0
, @total_recycle decimal(38,4) = 0


select @total_supply= sum(ISNULL(quantity,0)) 
, @total_new_part = sum(ISNULL(quantity_new_part,0)) 
, @total_recycle = sum(ISNULL(quantity_recycle,0)) 
from vw_ms_part_list_detail_supply
where ms_part_list_process_detail_id=@ms_part_list_change_part_detail_id and ms_process_id = 2


declare @sta_end_date date 
SELECT        TOP (1) @sta_end_date=end_date
FROM            dt_ms_cost_final_avg
WHERE        (ms_cost_final_avg_id = 0)

set @XMLMAPING =
' ';

set @TABELDATA=
' FROM vw_part_list_supply_available left outer join ( select ms_item_out_detail_id, quantity, ms_part_list_out_id from dt_ms_part_list_out where (ms_part_list_process_detail_id = @ms_part_list_change_part_detail_id and ms_process_id = 2) ) AS used_table ON vw_part_list_supply_available.ms_production_in_detail_id = used_table.ms_item_out_detail_id ';

set @fixWhere ='((vw_part_list_supply_available.ms_item_id = ' + convert(varchar(20),@check_ms_item_id) +')) 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' SELECT @jmlrow=count(*) ,@total_avialable= sum(isnull(vw_part_list_supply_available.quantity_sisa,0))' +@TABELDATA+ @rumusWhere + '' ;
SET @ParmDefinition = N'@sta_end_date date , @ms_part_list_change_part_detail_id bigint, @check_ms_item_id bigint, @check_doc_date date , @jmlrow bigint OUTPUT, @total_avialable decimal(38,4) OUTPUT';

EXECUTE sp_executesql  @SQLString, @ParmDefinition
 ,@sta_end_date=@sta_end_date
 ,@ms_part_list_change_part_detail_id = @ms_part_list_change_part_detail_id
 ,@check_ms_item_id =@check_ms_item_id  
 ,@check_doc_date = @check_doc_date   
 ,@jmlrow = @jmlrow OUTPUT 
 ,@total_avialable=@total_avialable 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'
 ,@total_avialable as 'qty_available'

 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 FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , vw_part_list_supply_available.ms_production_in_detail_id AS rID , vw_part_list_supply_available.doc_from AS r1 , vw_part_list_supply_available.receive_no AS r2 , dbo.date_to_str(vw_part_list_supply_available.receive_date ) AS r3 , vw_part_list_supply_available.doc_no AS r4 , dbo.date_to_str(vw_part_list_supply_available.doc_date ) AS r5 , vw_part_list_supply_available.quantity_sisa AS r6 , vw_part_list_supply_available.uom AS r7 , vw_part_list_supply_available.ms_item_id AS r8 , vw_part_list_supply_available.is_sumber AS r9 , cast(case when used_table.ms_item_out_detail_id is null then 0 else 1 end as bit) as r_u '
+ @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'@sta_end_date date , @ms_part_list_change_part_detail_id bigint, @check_ms_item_id bigint ,@check_doc_date date , @raw XML OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_part_list_change_part_detail_id = @ms_part_list_change_part_detail_id
,@check_ms_item_id=@check_ms_item_id  
,@check_doc_date = @check_doc_date   
,@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) + ''","r_u":"'' + dbo.JSONEscaped(r_u) + ''"}'' FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , vw_part_list_supply_available.ms_production_in_detail_id AS rID , vw_part_list_supply_available.doc_from AS r1 , vw_part_list_supply_available.receive_no AS r2 , vw_part_list_supply_available.receive_date AS r3 , vw_part_list_supply_available.doc_no AS r4 , vw_part_list_supply_available.doc_date AS r5 , vw_part_list_supply_available.quantity_sisa AS r6 , vw_part_list_supply_available.uom AS r7 , vw_part_list_supply_available.ms_item_id AS r8 , vw_part_list_supply_available.is_sumber AS r9 , cast(case when used_table.ms_item_out_detail_id is null then 0 else 1 end as bit) as r_u '
 + @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'@sta_end_date date , @ms_part_list_change_part_detail_id bigint, @check_ms_item_id bigint,@check_doc_date date , @rowJSON varchar(max) OUTPUT';
EXECUTE sp_executesql  @SQLString, @ParmDefinition
,@sta_end_date=@sta_end_date
,@ms_part_list_change_part_detail_id = @ms_part_list_change_part_detail_id
,@check_ms_item_id  =@check_ms_item_id  
,@check_doc_date = @check_doc_date   
,@rowJSON = @rowJSON OUTPUT 

--print @SQLString
set  @rowJSON ='{"userdata":{' +
'"total_avialable":"'+cast(cast( isnull(@total_avialable,0) as decimal(38,4))as varchar(255)) +'"' +
',"total_quantity":"'+cast(cast( isnull(@total_quantity,0) as decimal(38,4))as varchar(255)) +'"' +
',"total_new_part":"'+ cast(cast( isnull(@total_new_part,0) as decimal(38,4))as varchar(255))   +'"'+
',"total_recycle":"'+ cast(cast( isnull(@total_recycle,0) as decimal(38,4))as varchar(255))   +'"'+
',"total_supply":"'+ cast(cast( isnull(@total_supply,0) as decimal(38,4))as varchar(255))   +'"'+
',"total_less":"'+ cast(cast( isnull(@total_quantity,0)-isnull(@total_supply,0) as decimal(38,4))as varchar(255)) +'"'+
'}, "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