dbo.spi_perhitungan_exim

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spi_perhitungan_exim
(@filename varchar(100), @id_upload_perhitungan_exim bigint, @tipe varchar(50),  @username varchar(100), @datadiff varchar(max) )
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+'\exim\perhitungan_exim'
 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_perhitungan_exim where id_upload_perhitungan_exim=@id_upload_perhitungan_exim and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  
  return 
 end
 
 exec(
' DECLARE @cnt INT SET @cnt = ( SELECT COUNT(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=NO;IMEX=1;Database='+@fullpath+
''', [''Laporan Exim$'']) WHERE F1=''kode_barang'' AND F2=''nama_barang'' AND F3=''qty'' AND F4=''unit'' ) IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE Laporan exim TIDAK SESUAI'' AS description RETURN END Declare @tbl_dt_perhitungan_exim table ( kode_barang varchar(100) null, nama_barang varchar(100) null, qty float null, unit varchar(100) null ); declare @datadif varchar(max) set @datadif = (select ''' + @datadiff + 
''') declare @datasplit varchar(8000) declare @id_bc261_exim_hdr bigint insert into @tbl_dt_perhitungan_exim ( kode_barang, nama_barang , qty, unit ) select kode_barang, nama_barang, qty, unit FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''Laporan Exim$'']) delete from @tbl_dt_perhitungan_exim where kode_barang = '''' or kode_barang is null if (select count(*) from @tbl_dt_perhitungan_exim where kode_barang not in (select kode_barang from tbl_kode_barang) ) > 0 begin select ''06'' as status, ''KODE BARANG TIDAK SESUAI CEK KEMBALI DATANYA'' as description return end declare @jam date set @jam = getdate() DECLARE a_cursor CURSOR FOR select f_result from dbo.fn_ParseCSVString (@datadif, ''|'') OPEN a_cursor FETCH NEXT FROM a_cursor INTO @datasplit WHILE @@FETCH_STATUS = 0 BEGIN -- begin here insert into tblbc261_perhitungan_exim_hdr ( [NAMATUJU] ,[ALMTTUJU] ,[NOKONTRAK] ,[TGLKONTRAK] ,[custom_bond] ,[kurs] ,[kdval] ,[jml_hari_kerja] ,[jenis_pekerjaan] ,[NPWPTUJU] ,[status_data] ,[create_date] ,[create_by] ,[update_date] ,[update_by] ) values ( (select data from dbo.Split(@datasplit, ''#'') where id=3) ,(select data from dbo.Split(@datasplit, ''#'') where id=4) ,(select data from dbo.Split(@datasplit, ''#'') where id=1) ,(select data from dbo.Split(@datasplit, ''#'') where id=2) ,(select data from dbo.Split(@datasplit, ''#'') where id=5) ,(select data from dbo.Split(@datasplit, ''#'') where id=7) ,(select data from dbo.Split(@datasplit, ''#'') where id=8) ,(select data from dbo.Split(@datasplit, ''#'') where id=6) ,(select data from dbo.Split(@datasplit, ''#'') where id=9) ,(select data from dbo.Split(@datasplit, ''#'') where id=10) ,''New'' ,@jam ,'''+@username+
''' ,@jam ,'''+@username+
''' ) set @id_bc261_exim_hdr = @@IDENTITY FETCH NEXT FROM a_cursor INTO @datasplit END CLOSE a_cursor DEALLOCATE a_cursor insert into [dbo].[tblbc261_perhitungan_exim_dtl] ( id_bc261_exim_hdr ,[kode_barang] ,[part_name_customs] ,[qty] ,[unit] ) select @id_bc261_exim_hdr, kode_barang, nama_barang, qty, unit from @tbl_dt_perhitungan_exim ')
 
 
 update tbl_upload_perhitungan_exim set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_upload_perhitungan_exim =@id_upload_perhitungan_exim
 
 --EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 
END
--go
 --exec('[spi_perhitungan_exim] @filename=''format_perhitungan_exim.xls'', @id_upload_perhitungan_exim=1, @tipe=''BC261_perhitungan_exim'', @username=''admin'', @datadiff=''8992/jkt#2014-10-07#pt amara#jl. balaraja raya balaraja banten#9000.00#360#11000#usd|'' ')
GO