dbo.MG_PEMI_RPT_DEVISA

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.MG_PEMI_RPT_DEVISA
@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) ,
    @date_printed date ,
   @start_date date,
    @end_date date,
   @mode int,
  @format varchar(255),
  @source varchar(255),
  @periode_devisa varchar(255),
  @year varchar(255),
  @month varchar(255),
  @PeriodeName3 varchar(max)

SELECT
 @rowspage=isnull(tempTable.xItem.value('@rowspage', 'int'),50) ,
 @page=isnull(tempTable.xItem.value('@page', 'int'),1) ,
 @desc=isnull(tempTable.xItem.value('@desc', 'bit'),0) ,
 @sort=isnull(tempTable.xItem.value('@sort', 'varchar(255)'),'rID') ,
 @date_printed= isnull(tempTable.xItem.value('date_printed[1]', 'varchar(255)'),'')   ,
 @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)'),'All'),
 @periode_devisa = isnull(tempTable.xItem.value('periode_devisa[1]', 'varchar(255)'),'All')  
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)

select @year = txt_value from dbo.SplitStr(@periode_devisa,'_') where Position = 1
select @month = txt_value from dbo.SplitStr(@periode_devisa,'_') where Position = 2

declare @PeriodeName varchar(255)
declare @PeriodeName2 varchar(255)

set @PeriodeName=  'PERIODE ' +  dbo.dateToIndStr(@start_date) +' S.D ' + dbo.dateToIndStr( @end_date)
set @PeriodeName2=  ' : ' + dbo.dateToIndStr(@start_date) +' S.D ' + dbo.dateToIndStr( @end_date)
set @PeriodeName3 = '( PERIODE : ' + case 
             when @month = 1 then 'Januari'
             when @month = 2 then 'Febuari'
             when @month = 3 then 'Maret'
             when @month = 4 then 'April'
             when @month = 5 then 'Mei'
             when @month = 6 then 'Juni'
             when @month = 7 then 'Juli'
             when @month = 8 then 'Agustus'
             when @month = 9 then 'September'
             when @month = 10 then 'Oktober'
             when @month = 11 then 'November'
             when @month = 12 then 'Desember'
             else ''
            end + ' ' + @year + ' )'
              
 
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
 

DECLARE @motherboard_id varchar(255)
DECLARE @XMLValidString varchar(max)


DECLARE @max_dec_detail_unit int
, @max_dec_detail_total int
, @max_dec_detail_bruto int
, @max_dec_detail_netto int
, @max_dec_header_total int
, @max_dec_header_valuta int
, @max_dec_header_tambahan int
, @max_dec_header_diskon int
, @max_dec_header_asuransi int
, @max_dec_header_freight int
 
IF @max_dec_detail_unit is null
 SET @max_dec_detail_unit=4
IF @max_dec_detail_total is null
 SET @max_dec_detail_total=4
IF @max_dec_header_total is null
 SET @max_dec_header_total=2
IF @max_dec_header_tambahan is null
 SET @max_dec_header_tambahan=2
IF @max_dec_header_diskon is null
 SET @max_dec_header_diskon=2
IF @max_dec_header_asuransi is null
 SET @max_dec_header_asuransi=2
IF @max_dec_header_freight is null
 SET @max_dec_header_freight=2
IF @max_dec_header_valuta is null
 SET @max_dec_header_valuta=2
IF @max_dec_detail_bruto is null
 SET @max_dec_detail_bruto=2
IF @max_dec_detail_netto is null
 SET @max_dec_detail_netto=2
  

IF @format =''
SET @format='pdf'

declare @xlsreadonly bit
set @xlsreadonly=1
declare @additional_id int
declare @additional_value int

DECLARE @XMLMAPING xml
DECLARE @TABELDATA varchar(max)

declare @detail xml
declare @totalpages bigint


declare @SISAROWS bigint
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

declare @param3 int = 0
, @param4 decimal(38,2) = 0
, @param5 decimal(38,4) = 0
, @param6 int = 0
, @param7 decimal(38,2) = 0
, @param8 decimal(38,4) = 0
, @param9 int = 0
, @param10 decimal(38,2) = 0
, @param11 decimal(38,4) = 0
, @param12 int = 0
, @param13 decimal(38,2) = 0
, @param14 decimal(38,4) = 0
, @param15 varchar(255) = ''
, @jumlah_dokumen_23 int
, @nilai_dokumen_23 decimal(38,2)
, @berat_dokumen_23 decimal(38,4)
, @jumlah_dokumen_271 int
, @nilai_dokumen_271 decimal(38,2)
, @berat_dokumen_271 decimal(38,4)
, @jumlah_dokumen_30 int
, @nilai_dokumen_30 decimal(38,2)
, @berat_dokumen_30 decimal(38,4)
, @jumlah_dokumen_27 int
, @nilai_dokumen_27 decimal(38,2)
, @berat_dokumen_27 decimal(38,4)

declare @tmp_data_bc23 table (
 id BIGINT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
 , tableid int
 , TANGGAL_DAFTAR date
 , KODE_VALUTA varchar(255)
 , CIF decimal(38,2)
 , BRUTO decimal(38,4)
)

declare @tmp_data_bc271 table (
 id BIGINT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
 , tableid int
 , TANGGAL_DAFTAR date
 , KODE_VALUTA varchar(255)
 , CIF decimal(38,2)
 , BRUTO decimal(38,4)
)

declare @tmp_data_bc30 table (
 id BIGINT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
 , tableid int
 , TANGGAL_DAFTAR date
 , KODE_VALUTA varchar(255)
 , CIF decimal(38,2)
 , BRUTO decimal(38,4)
)

declare @tmp_data_bc27 table (
 id BIGINT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
 , tableid int
 , TANGGAL_DAFTAR date
 , KODE_VALUTA varchar(255)
 , CIF decimal(38,2)
 , BRUTO decimal(38,4)
)

if @source = 'WH' OR @source = 'ALL'
 begin

  INSERT INTO @tmp_data_bc23 (tableid, TANGGAL_DAFTAR, KODE_VALUTA, CIF, BRUTO) 
  Select 1 as tableid, a.BC23Tg ,a.Kdval, a.NilInv, a.Bruto from tbl_bc_in_hdr a
   Left Join tbl_data_in_hdr b ON a.id_bc_in_hdr = b.id_terkait AND doc_in_from_id = 3
  where a.BC23NO is not null 
   AND a.BC23TG is not null 
   AND DATEPART(year,a.BC23TG) = @year
   AND DATEPART(month,a.BC23TG) = @month
   --AND DATEPART(year,b.doc_in_date) = @year
   --AND DATEPART(month,b.doc_in_date) = @month
   AND a.typedok IN ('BC23')

  UNION ALL

  select 2 as tableid, a.TANGGAL_DAFTAR,a.KODE_VALUTA, a.CIF, a.BRUTO from dt_ms_tpb_in_header a
   Left Join dt_ms_tpb_in_header_h_status b ON a.dt_ms_tpb_in_header_h_status_id = b.dt_ms_tpb_in_header_h_status_id
  Where b.status_tpb_in_id = 3
   AND DATEPART(year,a.TANGGAL_DAFTAR) = @year
   AND DATEPART(month,a.TANGGAL_DAFTAR) = @month
   --AND DATEPART(year,a.receive_date) = @year
   --AND DATEPART(month,a.receive_date) = @month
   AND KODE_DOKUMEN_PABEAN = '23'
  
  select @jumlah_dokumen_23 = isnull(count(id),0) from @tmp_data_bc23
  set @param3 = @param3 + @jumlah_dokumen_23

  select @nilai_dokumen_23 = isnull(sum((isnull(CIF,0) * dbo.get_rate_valuta(TANGGAL_DAFTAR,KODE_VALUTA)) / dbo.get_rate_valuta(TANGGAL_DAFTAR,'USD')),0) from @tmp_data_bc23
  set @param4 = @param4 + @nilai_dokumen_23

  select @berat_dokumen_23 = isnull(sum(isnull(BRUTO,0) / 1000),0) from @tmp_data_bc23
  set @param5 = @param5 + @berat_dokumen_23

  -----------------------------------------------------------------------------------

  INSERT INTO @tmp_data_bc271 (tableid, TANGGAL_DAFTAR, KODE_VALUTA, CIF, BRUTO) 
  Select 1 as tableid, a.BC23Tg ,a.Kdval, a.NilInv, a.Bruto from tbl_bc_in_hdr a
   Left Join tbl_data_in_hdr b ON a.id_bc_in_hdr = b.id_terkait AND doc_in_from_id = 4
  where a.BC23NO is not null 
   AND a.BC23TG is not null 
   AND DATEPART(year,a.BC23TG) = @year
   AND DATEPART(month,a.BC23TG) = @month
   AND a.typedok IN ('BC271')
    
  UNION ALL

  select 2 as tableid, a.TANGGAL_DAFTAR,a.KODE_VALUTA, a.CIF, a.BRUTO from dt_ms_tpb_in_header a
   Left Join dt_ms_tpb_in_header_h_status b ON a.dt_ms_tpb_in_header_h_status_id = b.dt_ms_tpb_in_header_h_status_id
  Where b.status_tpb_in_id = 3
   AND DATEPART(year,a.TANGGAL_DAFTAR) = @year
   AND DATEPART(month,a.TANGGAL_DAFTAR) = @month
   AND KODE_DOKUMEN_PABEAN = '271'
  
  select @jumlah_dokumen_271 = isnull(count(id),0) from @tmp_data_bc271
  set @param6 = @param6 + @jumlah_dokumen_271

  select @nilai_dokumen_271 = isnull(sum((isnull(CIF,0) * dbo.get_rate_valuta(TANGGAL_DAFTAR,KODE_VALUTA)) / dbo.get_rate_valuta(TANGGAL_DAFTAR,'USD')),0) from @tmp_data_bc271
  set @param7 = @param7 + @nilai_dokumen_271

  select @berat_dokumen_271 = isnull(sum(isnull(BRUTO,0) / 1000),0) from @tmp_data_bc271
  set @param8 = @param8 + @berat_dokumen_271

  ---------------------------------------------------------------------------------------------------

  INSERT INTO @tmp_data_bc30 (tableid, TANGGAL_DAFTAR, KODE_VALUTA, CIF, BRUTO) 
  select 1 as tableid, a.TGDAFT, a.KDVAL, a.NilInv, a.BRUTO  from tbl_bc_out_hdr a
   Left Join tbl_data_out_hdr b ON a.id_bc_out_hdr = b.id_terkait AND b.doc_out_to_id = 2
  where a.NODAFT is not null 
   AND a.TGDAFT is not null
   AND DATEPART(year,a.TGDAFT) = @year
   AND DATEPART(month,a.TGDAFT) = @month
   AND a.typedok IN ('BC30')

  UNION ALL

  select 2 as tableid, a.TANGGAL_DAFTAR,a.KODE_VALUTA, a.CIF, a.BRUTO from dt_ms_tpb_out_header a
   Left Join dt_ms_tpb_out_header_h_status b ON a.dt_ms_tpb_out_header_h_status_id = b.dt_ms_tpb_out_header_h_status_id
  Where b.status_tpb_out_id = 3
   AND DATEPART(year,a.TANGGAL_DAFTAR) = @year
   AND DATEPART(month,a.TANGGAL_DAFTAR) = @month
   AND KODE_DOKUMEN_PABEAN = '30'
  
  select @jumlah_dokumen_30 = isnull(count(id),0) from @tmp_data_bc30
  set @param9 = @param9 + @jumlah_dokumen_30

  select @nilai_dokumen_30 = isnull(sum((isnull(CIF,0) * dbo.get_rate_valuta(TANGGAL_DAFTAR,KODE_VALUTA)) / dbo.get_rate_valuta(TANGGAL_DAFTAR,'USD')),0) from @tmp_data_bc30
  set @param10 = @param10 + @nilai_dokumen_30

  select @berat_dokumen_30 = isnull(sum(isnull(BRUTO,0) / 1000),0) from @tmp_data_bc30
  set @param11 = @param11 + @berat_dokumen_30

  ---------------------------------------------------------------------------------------------------

  INSERT INTO @tmp_data_bc27 (tableid, TANGGAL_DAFTAR, KODE_VALUTA, CIF, BRUTO) 
  select 1 as tableid, a.TGDAFT, a.KDVAL, a.NilInv, a.BRUTO  from tbl_bc_out_hdr a
   Left Join tbl_data_out_hdr b ON a.id_bc_out_hdr = b.id_terkait AND b.doc_out_to_id = 5
  where a.NODAFT is not null 
   AND a.TGDAFT is not null
   AND DATEPART(year,a.TGDAFT) = @year
   AND DATEPART(month,a.TGDAFT) = @month
   AND a.typedok IN ('BC27')

  UNION ALL

  select 2 as tableid, a.TANGGAL_DAFTAR,a.KODE_VALUTA, a.CIF, a.BRUTO from dt_ms_tpb_out_header a
   Left Join dt_ms_tpb_out_header_h_status b ON a.dt_ms_tpb_out_header_h_status_id = b.dt_ms_tpb_out_header_h_status_id
  Where b.status_tpb_out_id = 3
   AND DATEPART(year,a.TANGGAL_DAFTAR) = @year
   AND DATEPART(month,a.TANGGAL_DAFTAR) = @month
   AND KODE_DOKUMEN_PABEAN = '27'
  
  select @jumlah_dokumen_27 = isnull(count(id),0) from @tmp_data_bc27
  set @param12 = @param12 + @jumlah_dokumen_27

  select @nilai_dokumen_27 = isnull(sum((isnull(CIF,0) * dbo.get_rate_valuta(TANGGAL_DAFTAR,KODE_VALUTA)) / dbo.get_rate_valuta(TANGGAL_DAFTAR,'USD')),0) from @tmp_data_bc27
  set @param13 = @param13 + @nilai_dokumen_27

  select @berat_dokumen_27 = isnull(sum(isnull(BRUTO,0) / 1000),0) from @tmp_data_bc27
  set @param14 = @param14 + @berat_dokumen_27


 end


if @source = 'AW' OR @source = 'ALL'
 begin
  set @source = @source
 end

set  @detail=(
    SELECT  top 1   
   @sort as 'sort' , 
   @desc as 'desc' , 
   1 as 'rowspage' , 
   1 as 'pages' ,
   1 as 'rows' ,
   @PeriodeName as 'periode_name',
   @PeriodeName2 as 'param1',
   @PeriodeName3 as 'param2',
   @PT_NAME as 'nama_pt',
   @maxdec as 'max_decimal_digit_price',
   @xlsreadonly as 'xls_readonly',
   dbo.additional_info(3110) as 'ada_invoice',
   CONVERT(varchar(255),NEWID()) as 'report_ID',
   @param3 as 'param3',
   @param4 as 'param4',
   @param5 as 'param5',
   @param6 as 'param6',
   @param7 as 'param7',
   @param8 as 'param8',
   @param9 as 'param9',
   @param10 as 'param10',
   @param11 as 'param11',
   @param12 as 'param12',
   @param13 as 'param13',
   @param14 as 'param14'

   FROM         APP_SETTING
   for xml path('detail') )
 
 
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