dbo.MG_PEMI_RPT_REKAP

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.MG_PEMI_RPT_REKAP
@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_rekap 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_rekap = isnull(tempTable.xItem.value('periode_rekap[1]', 'varchar(255)'),'All')  
FROM @PARAMETER.nodes('param/field')  tempTable(xItem)

select @year = txt_value from dbo.SplitStr(@periode_rekap,'_') where Position = 1
select @month = txt_value from dbo.SplitStr(@periode_rekap,'_') 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 int = 0
, @param5 int = 0
, @param6 int = 0
, @param7 int = 0
, @param8 int = 0
, @param9 int = 0
, @param10 int = 0
, @param11 int = 0
, @param12 int = 0
, @param13 int = 0
, @param14 int = 0
, @param15 int = 0
, @param16 int = 0
, @param17 int = 0
, @param18 int = 0
, @param19 int = 0
, @param20 int = 0
, @param21 int = 0
, @param22 int = 0
, @param23 int = 0
, @param24 int = 0
, @param25 decimal(38,2) = 0
, @param26 decimal(38,2) = 0
, @param27 decimal(38,2) = 0
, @param28 decimal(38,2) = 0

, @value3 int = 0
, @value4 int = 0
, @value5 int = 0
, @value6 int = 0
, @value7 int = 0
, @value8 int = 0
, @value9 int = 0
, @value10 int = 0
, @value11 int = 0
, @value12 int = 0
, @value13 int = 0
, @value14 int = 0
, @value15 int = 0
, @value16 int = 0
, @value17 int = 0
, @value18 int = 0
, @value19 int = 0
, @value20 int = 0
, @value21 int = 0
, @value22 int = 0
, @value23 int = 0
, @value24 int = 0
, @value25 decimal(38,2) = 0
, @value26 decimal(38,2) = 0
, @value27 decimal(38,2) = 0
, @value28 decimal(38,2) = 0

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_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)
 , JnEks int
)

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 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 KODE_DOKUMEN_PABEAN = '23'
  
  select @value3 = isnull(count(id),0) from @tmp_data_bc23
  set @param3 = @param3 + @value3

  --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

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

  Select @value5 = isnull(count(tableid),0) from (
   Select 1 as tableid 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 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'
  ) a
  
  set @param5 = @param5 + @value5

  -----------------------------------------------------------------------------------
  Select @value6 = isnull(count(tableid),0) from (
   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 a.typedok IN ('BC262')
    AND tujuan_pemasukan = 2
    
   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 = '262'
    AND KODE_TUJUAN_PEMASUKAN = 2
  ) a

  set @param6 = @param6 + @value6

  -----------------------------------------------------------------------------------
  Select @value7 = isnull(count(tableid),0) from (
   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')
    AND tujuan_pengiriman = 1
    
   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'
    AND KODE_TUJUAN_PENGIRIMAN = 1
  ) a

  set @param7 = @param7 + @value7

  -----------------------------------------------------------------------------------
  Select @value8 = isnull(count(tableid),0) from (
   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 = 5
   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 ('BC40')
    AND tujuan_pengiriman = 1
    
   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 = '40'
    AND KODE_TUJUAN_PENGIRIMAN = 1
  ) a

  set @param8 = @param8 + @value8

  -----------------------------------------------------------------------------------
  Select @value9 = isnull(count(tableid),0) from (
   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 a.typedok IN ('BC262')
    AND (tujuan_pemasukan = 1 or tujuan_pemasukan = 3)
    
   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 = '262'
    AND (KODE_TUJUAN_PEMASUKAN = 1 OR KODE_TUJUAN_PEMASUKAN = 3)
  ) a

  set @param9 = @param9 + @value9

  -----------------------------------------------------------------------------------
  Select @value10 = isnull(count(tableid),0) from (
   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')
    AND (tujuan_pengiriman = 2 or tujuan_pengiriman = 3 or tujuan_pengiriman = 4)
    
   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'
    AND (KODE_TUJUAN_PENGIRIMAN = 2 OR KODE_TUJUAN_PENGIRIMAN = 3 OR KODE_TUJUAN_PENGIRIMAN = 4)
  ) a

  set @param10 = @param10 + @value10

  -----------------------------------------------------------------------------------
  Select @value11 = isnull(count(tableid),0) from (
   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 = 5
   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 ('BC40')
    AND (tujuan_pengiriman = 2 or tujuan_pengiriman = 3 or tujuan_pengiriman = 4)
    
   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 = '40'
    AND (KODE_TUJUAN_PENGIRIMAN = 2 OR KODE_TUJUAN_PENGIRIMAN = 3 OR KODE_TUJUAN_PENGIRIMAN = 4)
  ) a

  set @param11 = @param11 + @value11

  -----------------------------------------------------------------------------------
  Select @value13 = isnull(count(tableid),0) from (
   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 = 5
   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 ('BC40')
    --AND (tujuan_pengiriman = 2 or tujuan_pengiriman = 3 or tujuan_pengiriman = 4)
    
   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 = '40'
    --AND (KODE_TUJUAN_PENGIRIMAN = 2 OR KODE_TUJUAN_PENGIRIMAN = 3 OR KODE_TUJUAN_PENGIRIMAN = 4)
  ) a

  set @param13 = @param13 + @value13

  --------------------------------------------------------------------------------------------------------------------------
  INSERT INTO @tmp_data_bc30 (tableid, TANGGAL_DAFTAR, KODE_VALUTA, CIF, BRUTO, JnEks) 
  select 1 as tableid, a.TGDAFT, a.KDVAL, a.NilInv, a.BRUTO, a.JnEks  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, 1 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 @value14 = isnull(count(id),0) from @tmp_data_bc30 where JnEks = 1 OR JnEks = 2
  set @param14 = @param14 + @value14
  
  --------------------------------------------------------------------------------------------------------------------------
  
  select @value15 = isnull(count(id),0) from @tmp_data_bc30 where JnEks = 3
  set @param15 = @param15 + @value15

  --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

  
 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',
   @param15 as 'param15',
   @param16 as 'param16',
   @param17 as 'param17',
   @param18 as 'param18',
   @param19 as 'param19',
   @param20 as 'param20',
   @param21 as 'param21',
   @param22 as 'param22',
   @param23 as 'param23',
   @param24 as 'param24',
   @param25 as 'param25',
   @param26 as 'param26',
   @param27 as 'param27',
   @param28 as 'param28'

   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