dbo.MG_MS_MOLTS_GRN

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

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

--declare @sub_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')
  ,@source=isnull(tempTable.xItem.value('source[1]', 'varchar(255)'),'') 
 ,@start_date_str =isnull(tempTable.xItem.value('start_date[1]', 'varchar(255)') ,'') 
 ,@end_date_str= isnull(tempTable.xItem.value('end_date[1]', 'varchar(255)'),'')  
 --,@sub_id=isnull(tempTable.xItem.value('sub_id[1]', 'bigint'),-1)
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)


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

----#####START SOURCE#####
----SELECT id, used, GRNUMBER, GRDATE, PARTNUMBER, PARTCODE, PARTUNIT, PARTETA, GRQTY, PARTPRICE, PONUMBER, PODATE, KODE_DOKUMEN_PABEAN, BC_TYPE, CAR, BC_DATE, BC_NO, STATUS_MODULE, SHIPPING, SHIPPER, create_date, create_by
----FROM vw_ms_molts_grn;
----#####END SOURCE#####

set @XMLMAPING =
' ';

  set @TABELDATA=case when @source='LP' then 
' FROM vw_ms_molts_grn_lookup (@start_date,@end_date) ' else  
' FROM vw_ms_molts_grn '
  end 
--set @TABELDATA='
-- FROM vw_ms_molts_grn


--';
set @fixWhere ='';
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'@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 @SISAROWS bigint=0;
set @totalpages=1;
if (@rowspage>0)
BEGIN
 set @SISAROWS =@jmlrow % @rowspage
 if @SISAROWS=0
  set @totalpages= @jmlrow/@rowspage
 Else
  set @totalpages=  ((@jmlrow-@SISAROWS)/@rowspage) + 1;
END

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 , r22, r23, r24, r25, r26 , r27 FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , id AS rID , used AS r1 , GRNUMBER AS r2 , dbo.date_to_str(GRDATE ) AS r3 , PARTNUMBER AS r4 , PARTCODE AS r5 , PARTUNIT AS r6 , dbo.date_to_str(PARTETA ) AS r7 , cast(GRQTY AS decimal(18,4)) AS r8 , cast(PARTPRICE AS decimal(18,4)) AS r9 , PONUMBER AS r10 , dbo.date_to_str(PODATE ) AS r11 , KODE_DOKUMEN_PABEAN AS r12 , BC_TYPE AS r13 , CAR AS r14 , BC_DATE AS r15 , BC_NO AS r16 , STATUS_MODULE AS r17 , SHIPPING AS r18 , SHIPPER AS r19 , dbo.date_to_str(create_date ) AS r20 , create_by AS r21 , dbo.date_to_str(mapping_date ) AS r22 , mapping_by AS r23 , mapping_supplier_customer_id AS r24 , mapped AS r25 , cleansing as r26 , kategori_internal as r27 '
+ @TABELDATA + @rumusWhere  + ' ) XDATA '
IF (@rowspage>0)
 set @SQLString = @SQLString +      'WHERE rN BETWEEN (' + cast((@page) as nvarchar(max)) +' -1) * ' + cast(@ROWSPAGE  as nvarchar(max)) + ' + 1 AND '+ CAST(@page * @ROWSPAGE as nvarchar(max))
set @SQLString = @SQLString +  ' for xml PATH(''xrow'')) '

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 

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) + ''","r22":"'' + dbo.JSONEscaped(r22) + ''","r23":"'' + dbo.JSONEscaped(r23) + ''","r24":"'' + dbo.JSONEscaped(r24) + ''","r25":"'' + dbo.JSONEscaped(r25) + ''","r26":"'' + dbo.JSONEscaped(r26) + ''","r27":"'' + dbo.JSONEscaped(r27) + ''"}'' FROM( SELECT ROW_NUMBER() OVER(ORDER BY ' + @OutSortBy + 
') as rn , id AS rID , used AS r1 , GRNUMBER AS r2 , dbo.date_to_str(GRDATE ) AS r3 , PARTNUMBER AS r4 , PARTCODE AS r5 , PARTUNIT AS r6 , dbo.date_to_str(PARTETA ) AS r7 , cast(GRQTY AS decimal(18,4)) AS r8 , cast(PARTPRICE AS decimal(18,4)) AS r9 , PONUMBER AS r10 , dbo.date_to_str(PODATE ) AS r11 , KODE_DOKUMEN_PABEAN AS r12 , BC_TYPE AS r13 , CAR AS r14 , BC_DATE AS r15 , BC_NO AS r16 , STATUS_MODULE AS r17 , SHIPPING AS r18 , SHIPPER AS r19 , dbo.date_to_str(create_date ) AS r20 , create_by AS r21 , dbo.date_to_str(mapping_date ) AS r22 , mapping_by AS r23 , mapping_supplier_customer_id AS r24 , mapped AS r25 , cleansing as r26 , kategori_internal as r27 '
 + @TABELDATA + @rumusWhere  + ' ) XDATA '
IF (@rowspage>0)
 set @SQLString = @SQLString +      'WHERE rN BETWEEN (' + cast((@page) as nvarchar(max)) +' -1) * ' + cast(@ROWSPAGE  as nvarchar(max)) + ' + 1 AND '+ CAST(@page * @ROWSPAGE as nvarchar(max))
set @SQLString = @SQLString +  ' for xml PATH(''''), type ).value(''.'', ''nvarchar(max)''), 1, 1, '''') + '']'')';

SET @ParmDefinition = N'@start_date date, @end_date date,@rowJSON nvarchar(max) OUTPUT';
--print @SQLString
EXECUTE sp_executesql  @SQLString, @ParmDefinition,
@start_date=@start_date,
@end_date=@end_date,
@rowJSON = @rowJSON OUTPUT 

--set @rowJSON =(select dbo.encodeBase64('{"page":"'+ cast(@page as varchar(255)) + '","total":"' + cast(@totalpages as varchar(255)) + '","records":"' + cast(@jmlrow as varchar(255)) +'","rows":' + isnull(@rowJSON,'{}') + '}'))
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 dbo.MG_MS_MOLTS_GRN
--'12dbo.MG_MS_MOLTS_GRN1202182.30.141.4702013012820130528'

GO