dbo.spi_validasi_pajak

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_validasi_pajak
(@filename varchar(100), @id_validasi_pajak bigint, @tipe varchar(50),  @username varchar(100), @typebahan varchar(10) )
AS
BEGIN
  --
  SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
    Declare @dir varchar(100)
 declare @filedir varchar(200)
 declare @filecount int
 declare @fullpath varchar(200)
 DECLARE @Files table(Names varchar(250) null)
 declare @dircmd varchar(500)
 declare @cmdmove varchar(500)




 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\pajak'
 set @fullpath=@filedir+'\'+@filename
 set @dircmd='dir '+@fullpath
 set @cmdmove = 'MOVE /Y '+ @filedir +'\'+ @FileName + ' '+ @filedir +'\log\' + @filename
  
 
 INSERT INTO @Files EXEC MASTER..XP_CMDSHELL @dircmd
 set @filecount = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 if @filecount = 1
 begin
  select '06' as status, names+' 0 ' as description from @Files  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_upload_validasi_pajak where id_validasi_pajak=@id_validasi_pajak and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  return 
 end
 if @tipe = 'a1'
 begin  
  exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +''', ['+ @tipe+'$' + 
']) WHERE F1=''pemberi_jkp'' AND F2=''kode_no_seri'' and F3=''tgl'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE PAJAK TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_pajak_tmp table ( [pemberi_jkp] varchar(50) NULL ,[npwp] varchar(50) NULL ,[kode_no_seri] varchar(50) NULL ,[tgl] varchar(50) NULL ,[dpp] float NULL ,[ppn] float NULL ,[ppnbm] float NULL ,[masa_spt] varchar(50) NULL ,[car] varchar(50) NULL ,[nobc] varchar(50) NULL ,[tglbc] date NULL ,[tipe_bc] varchar(50) NULL ,[tipe_pajak] varchar(50) NULL ); insert into @tbl_pajak_tmp ( [pemberi_jkp] --,[npwp] ,[kode_no_seri] ,[tgl] ,[dpp] -- ,[ppn] -- ,[ppnbm] ,masa_spt ,[car] ,[nobc] ,[tglbc] ,[tipe_bc] ,[tipe_pajak] ) select [pemberi_jkp] --,[npwp] ,[kode_no_seri] ,[tgl] ,[dpp] -- ,[ppn] -- ,[ppnbm] ,masa_spt ,[car] ,[nobc] ,[tglbc] ,[tipe_bc] ,''' + @tipe + 
''' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +''', ['+ @tipe+'$' + 
']) delete from @tbl_pajak_tmp where pemberi_jkp is null declare @kodeseri table ( id_b123 bigint ) insert into @kodeseri ( id_b123 ) select a.id_b123 from [tbl_pajak_b123] a inner join @tbl_pajak_tmp b on a.kode_no_seri = b.kode_no_seri and a.tipe_pajak = b.tipe_pajak delete from [tbl_pajak_b123] where [id_b123] in (select a.id_b123 from @kodeseri a group by a.id_b123) insert into tbl_pajak_b123 ( [pemberi_jkp] ,[npwp] ,[kode_no_seri] ,[tgl] ,[tglx] ,[dpp] ,[ppn] ,[ppnbm] ,masa_spt ,[car] ,[nobc] ,[tglbc] ,[tipe_bc] ,[tipe_pajak] ,periode ) select [pemberi_jkp] ,[npwp] ,[kode_no_seri] ,(iif(ISDATE(dbo.pajaktgl(tgl))=1,dbo.pajaktgl(tgl),NULL)) as tgl ,[tgl] as tglx ,[dpp] ,[ppn] ,[ppnbm] ,masa_spt ,isnull([car], (select top 1 CAR from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,isnull([nobc], (select top 1 [bcno] from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,isnull([tglbc], (select top 1 bctg from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,isnull([tipe_bc], (select top 1 status_bc from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,[tipe_pajak] ,SUBSTRING(REPLACE(dbo.pajaktgl(tgl),''-'',''''),1,6) from @tbl_pajak_tmp ')
 
 
  update tbl_upload_validasi_pajak set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_validasi_pajak =@id_validasi_pajak
 
  EXEC xp_cmdshell @cmdmove, NO_OUTPUT
  select '00' as status, 'DATA SUKSES PROCESSED' as description
 end
 else
 begin
  exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath +''', ['+ @tipe+'$' + 
']) WHERE F1=''pemberi_jkp'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE PAJAK TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() Declare @tbl_pajak_tmp table ( [pemberi_jkp] varchar(50) NULL ,[npwp] varchar(50) NULL ,[kode_no_seri] varchar(50) NULL ,[tgl] varchar(50) NULL ,[dpp] float NULL ,[ppn] float NULL ,[ppnbm] float NULL ,[masa_spt] varchar(50) NULL ,[car] varchar(50) NULL ,[nobc] varchar(50) NULL ,[tglbc] date NULL ,[tipe_bc] varchar(50) NULL ,[tipe_pajak] varchar(50) NULL ); insert into @tbl_pajak_tmp ( [pemberi_jkp] --,[npwp] ,[kode_no_seri] ,[tgl] ,[dpp] ,[ppn] ,[ppnbm] ,masa_spt ,[car] ,[nobc] ,[tglbc] ,[tipe_bc] ,[tipe_pajak] ) select [pemberi_jkp] --,[npwp] ,[kode_no_seri] ,[tgl] ,[dpp] ,[ppn] ,[ppnbm] ,masa_spt ,[car] ,[nobc] ,[tglbc] ,[tipe_bc] ,''' + @tipe + 
''' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +''', ['+ @tipe+'$' + 
']) delete from @tbl_pajak_tmp where pemberi_jkp is null declare @kodeseri table ( id_b123 bigint ) insert into @kodeseri ( id_b123 ) select a.id_b123 from [tbl_pajak_b123] a inner join @tbl_pajak_tmp b on a.kode_no_seri = b.kode_no_seri and a.tipe_pajak = b.tipe_pajak delete from [tbl_pajak_b123] where [id_b123] in (select a.id_b123 from @kodeseri a group by a.id_b123) insert into tbl_pajak_b123 ( [pemberi_jkp] ,[npwp] ,[kode_no_seri] ,[tgl] ,[tglx] ,[dpp] ,[ppn] ,[ppnbm] ,masa_spt ,[car] ,[nobc] ,[tglbc] ,[tipe_bc] ,[tipe_pajak] ,periode ) select [pemberi_jkp] ,[npwp] ,[kode_no_seri] ,(iif(ISDATE(dbo.pajaktgl(tgl))=1,dbo.pajaktgl(tgl),NULL)) as tgl ,[tgl] as tglx ,[dpp] ,[ppn] ,[ppnbm] ,masa_spt ,isnull([car], (select top 1 CAR from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,isnull([nobc], (select top 1 [bcno] from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,isnull([tglbc], (select top 1 bctg from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,isnull([tipe_bc], (select top 1 status_bc from [dbo].[f_pajakbc]([kode_no_seri],[tgl],''' + @tipe + 
''')) ) ,[tipe_pajak] ,SUBSTRING(REPLACE(dbo.pajaktgl(tgl),''-'',''''),1,6) from @tbl_pajak_tmp ')
 
 
  update tbl_upload_validasi_pajak set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_validasi_pajak =@id_validasi_pajak
 
  EXEC xp_cmdshell @cmdmove, NO_OUTPUT
  select '00' as status, 'DATA SUKSES PROCESSED' as description

 end
 
END
--go
--BEGIN TRY
-- exec('spi_validasi_pajak @filename=''B1.xls'', @id_validasi_pajak=2, @tipe=''b1'', @username=''admin'', @typebahan=''b1'' ')
-- END TRY
-- BEGIN CATCH
-- SELECT
-- 'error' AS status
-- ,ERROR_MESSAGE() AS description;
    
-- END CATCH;
GO