dbo.spi_getdata_bc25

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.spi_getdata_bc25 
 -- Add the parameters for the stored procedure here
(@filename varchar(100), @idfile bigint, @username varchar(70)  )
AS
BEGIN
 SET ANSI_NULLS ON
 SET ANSI_WARNINGS 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)
 declare @tbl_sa_temp varchar(255), @tbl_sa_bb_temp varchar(255), @tbl_sa_hdr_temp varchar(255)
 set @dir = (select top 1 direktori from dbo.tbl_direktori)
 set @filedir=@dir+'\exim\bc25'
 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_bc25] where id_bc25_upload=@idfile and status_upload<>'SUKSES') <>0
 begin
  select '07' as status, 'DATA SUDAH DIPORSES' as description 
  --return
 end
 --declare @hasil varchar(50)
 --exec [dbo].[spu_seq] @nomor_untuk = N'nobc25', @hasil = @hasil OUTPUT
  -- declare @doc_bc25_no varchar(50)
  -- set @doc_bc25_no = @hasil
 
 BEGIN TRY
 
 exec(
' DECLARE @cnt INT, @id_sa_hdr bigint, @id_sa_hdrx bigint set @cnt = (select COUNT(*) as cnt FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@filename+
'] '') where F1 = ''H0'' or F1=''D0'') IF @cnt<>2 BEGIN SELECT ''06'' AS status, ''FILE DATA bc25 TIDAK SESUAI'' AS description RETURN END declare @jam datetime set @jam=GETDATE() declare @sa_no varchar(50) set @sa_no=( select top 1 F2 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@filename+
'] '') where F1 = ''H1'' group by F2 ) declare @tbl_sa_hdr_temp table ( [SA_NO] varchar(50) null ) declare @sano varchar(100) = replace(@sa_no, '' '', '''') insert into @tbl_sa_hdr_temp ( [SA_NO] ) values ( @sa_no ) if (select count(*) from tbl_sa_hdr where replace(SA_NO, '' '', '''') = @sano) = 0 begin insert into dbo.tbl_sa_hdr ( [SA_NO] ,BC25TGAJU1 ,status , create_by, update_by, create_date, update_date ) select [SA_NO] ,getdate() ,''New'' ,'''+@username+''','''+@username+
''' ,@jam,@jam from @tbl_sa_hdr_temp --set @id_sa_hdr = SCOPE_IDENTITY() set @id_sa_hdr = @@IDENTITY end else begin set @id_sa_hdrx = (select top 1 id_sa_hdr from tbl_sa_hdr where replace(SA_NO, '' '', '''') = @sano) delete from dbo.tbl_sa_hdr where id_sa_hdr = @id_sa_hdrx delete from dbo.tbl_sa where id_sa_hdr = @id_sa_hdrx delete from dbo.tbl_sa_dok where id_sa_hdr = @id_sa_hdrx delete from dbo.tbl_sa_bom where id_sa_hdr = @id_sa_hdrx insert into dbo.tbl_sa_hdr ( [SA_NO] ,BC25TGAJU1 ,status , create_by, update_by, create_date, update_date ) select [SA_NO] ,getdate() ,''New'' ,'''+@username+''','''+@username+
''' ,@jam,@jam from @tbl_sa_hdr_temp --set @id_sa_hdr = SCOPE_IDENTITY() set @id_sa_hdr = @@IDENTITY end declare @tbl_sa_temp table ( SA_NO varchar(50) NULL, NPWP_PEMBELI varchar(50) NULL, NAMA_PEMBELI varchar(50) NULL, ALAMAT varchar(90) NULL, INV_NO varchar(50) NULL, ASSY_CODE varchar(50) NULL, ASSY_NO varchar(50) NULL, TOTAL_SET float NULL, SATUAN varchar(5) NULL, JUMLAH_COLI float NULL, GROSSWEIGHT float NULL, SATUANGROSS varchar(5) NULL, NETWEIGHT float NULL, SATUANNET varchar(5), PRICE float NULL, AMOUNT float NULL, TGL_INV varchar(15) NULL, KD_VAL varchar(5) NULL ) insert into @tbl_sa_temp ( [SA_NO] ,[NPWP_PEMBELI] ,[NAMA_PEMBELI] ,[ALAMAT] ,[INV_NO] ,[ASSY_CODE] ,[ASSY_NO] ,[TOTAL_SET] ,[SATUAN] ,[JUMLAH_COLI] ,[GROSSWEIGHT] ,[SATUANGROSS] ,[NETWEIGHT] ,[SATUANNET] ,[PRICE] ,[AMOUNT] ,[KD_VAL] ,[TGL_INV] ) select F2, F3, F4, F5, F6, F7, F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@filename+
'] '') where F1 = ''H1'' MERGE dbo.tbl_sa AS TARGET USING @tbl_sa_temp AS SOURCE ON (replace(TARGET.SA_NO, '' '', '''') = replace(SOURCE.SA_NO, '' '', '''')) WHEN MATCHED AND TARGET.ASSY_CODE = SOURCE.ASSY_CODE THEN UPDATE SET TARGET.TOTAL_SET = SOURCE.TOTAL_SET, TARGET.SATUAN = SOURCE.SATUAN, TARGET.JUMLAH_COLI = SOURCE.JUMLAH_COLI, TARGET.GROSSWEIGHT = SOURCE.GROSSWEIGHT, TARGET.SATUANGROSS = SOURCE.SATUANGROSS, TARGET.NETWEIGHT = SOURCE.NETWEIGHT, TARGET.SATUANNET = SOURCE.SATUANNET, TARGET.PRICE = SOURCE.PRICE, TARGET.AMOUNT = SOURCE.AMOUNT, TARGET.KD_VAL = SOURCE.KD_VAL, TARGET.TGL_INV = SOURCE.TGL_INV, TARGET.HS_KODE = ''85443019'', TARGET.update_by = '''+@username+
''', TARGET.update_date = @jam WHEN NOT MATCHED BY TARGET THEN INSERT ( id_sa_hdr ,[SA_NO] ,[NPWP_PEMBELI] ,[NAMA_PEMBELI] ,[ALAMAT] ,[INV_NO] ,[ASSY_CODE] ,[ASSY_NO] ,[TOTAL_SET] ,[SATUAN] ,[JUMLAH_COLI] ,[GROSSWEIGHT] ,[SATUANGROSS] ,[NETWEIGHT] ,[SATUANNET] ,[PRICE] ,[AMOUNT] ,[KD_VAL] ,[TGL_INV] ,[HS_KODE] ,create_by, update_by, create_date, update_date) values ( @id_sa_hdr ,SOURCE.SA_NO ,SOURCE.NPWP_PEMBELI ,SOURCE.NAMA_PEMBELI ,SOURCE.ALAMAT ,SOURCE.INV_NO ,SOURCE.ASSY_CODE ,SOURCE.ASSY_NO ,SOURCE.TOTAL_SET ,SOURCE.SATUAN ,SOURCE.JUMLAH_COLI ,SOURCE.GROSSWEIGHT ,SOURCE.SATUANGROSS ,SOURCE.NETWEIGHT ,SOURCE.SATUANNET ,SOURCE.PRICE ,SOURCE.AMOUNT ,SOURCE.KD_VAL ,SOURCE.TGL_INV ,''85443019'' ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; declare @tbl_sa_bb_temp table ( SA_NO varchar(50) NULL, NO_URUT int NULL, PART_NAME varchar(50) NULL, QTY float NULL, UNIT varchar(5) NULL, STATUS varchar(50) NULL, CURR varchar(5) NULL, PRICE float NULL, CIF float NULL, NDPMB float NULL, HARGA_PABEAN float NULL, HS varchar(20) NULL, BM float NULL, PPN float NULL, PPH float NULL, BM_RP float NULL, PPN_RP float NULL, PPH_RP float NULL ) insert into @tbl_sa_bb_temp ( [SA_NO] ,[NO_URUT] ,[PART_NAME] ,[QTY] ,[UNIT] ,[STATUS] ,[CURR] ,[PRICE] ,[CIF] ,[NDPMB] ,[HARGA_PABEAN] ,[HS] ,[BM] ,[PPN] ,[PPH] ,[BM_RP] ,[PPN_RP] ,[PPH_RP] ) select F2, F3, F4, F5, F6, F7, F8,F9,F10,F11,F12,F13,F14, replace(F15,''%'',''''),replace(F16,''%'',''''),F17,F18,F19 FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'', ''Text;Database='+@filedir+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@filename+
'] '') where F1 = ''D1'' MERGE [tbl_sa_bb] AS TARGET USING @tbl_sa_bb_temp AS SOURCE ON (replace(TARGET.SA_NO, '' '', '''') = replace(SOURCE.SA_NO, '' '', '''')) WHEN MATCHED AND TARGET.NO_URUT = SOURCE.NO_URUT THEN UPDATE SET TARGET.PART_NAME = SOURCE.PART_NAME, TARGET.QTY = SOURCE.QTY, TARGET.UNIT = SOURCE.UNIT, TARGET.STATUS = SOURCE.STATUS, TARGET.CURR = SOURCE.CURR, TARGET.PRICE = SOURCE.PRICE, TARGET.NDPMB = SOURCE.NDPMB, TARGET.HARGA_PABEAN = SOURCE.HARGA_PABEAN, TARGET.HS = SOURCE.HS, TARGET.BM = SOURCE.BM, TARGET.PPN = SOURCE.PPN, TARGET.PPH = SOURCE.PPH, TARGET.BM_RP = SOURCE.BM_RP, TARGET.PPN_RP = SOURCE.PPN_RP, TARGET.PPH_RP = SOURCE.PPH_RP, TARGET.update_by = '''+@username+
''', TARGET.update_date = @jam WHEN NOT MATCHED BY TARGET THEN INSERT ( id_sa_hdr ,[SA_NO] ,[NO_URUT] ,[PART_NAME] ,[QTY] ,[UNIT] ,[STATUS] ,[CURR] ,[PRICE] ,[CIF] ,[NDPMB] ,[HARGA_PABEAN] ,[HS] ,[BM] ,[PPN] ,[PPH] ,[BM_RP] ,[PPN_RP] ,[PPH_RP] ,create_by, update_by, create_date, update_date) values ( @id_sa_hdr ,SOURCE.SA_NO ,SOURCE.NO_URUT ,SOURCE.PART_NAME ,SOURCE.QTY ,SOURCE.UNIT ,SOURCE.STATUS ,SOURCE.CURR ,SOURCE.PRICE ,SOURCE.CIF ,SOURCE.NDPMB ,SOURCE.HARGA_PABEAN ,SOURCE.HS ,SOURCE.BM ,SOURCE.PPN ,SOURCE.PPH ,SOURCE.BM_RP ,SOURCE.PPN_RP ,SOURCE.PPH_RP ,'''+@username+''','''+@username+
''' ,@jam,@jam) ; if (select count(*) from tbl_sa_dok where id_sa_hdr = @id_sa_hdr) = 0 begin insert into [dbo].[tbl_sa_dok] ( [id_sa_hdr] ,CAR ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_sa_hdr ,''-'' ,''380'' ,[INV_NO] ,CONVERT(DATE, SUBSTRING(TGL_INV, 3, 2) + ''/'' + SUBSTRING(TGL_INV, 1, 2) + ''/'' + SUBSTRING(TGL_INV, 5, 4)) from [dbo].[tbl_sa] where [id_sa_hdr] = @id_sa_hdr group by [INV_NO] ,[TGL_INV] ,SA_NO insert into [dbo].[tbl_sa_dok] ( [id_sa_hdr] ,CAR ,[KdDok] ,[NoDok] ,[TgDok] ) select @id_sa_hdr ,[SA_NO] ,''217'' ,[INV_NO] ,CONVERT(DATE, SUBSTRING(TGL_INV, 3, 2) + ''/'' + SUBSTRING(TGL_INV, 1, 2) + ''/'' + SUBSTRING(TGL_INV, 5, 4)) from [dbo].[tbl_sa] where [id_sa_hdr] = @id_sa_hdr group by [INV_NO] ,[TGL_INV] ,SA_NO end ')
 
 END TRY
  BEGIN CATCH
  select ERROR_NUMBER() as status, ERROR_MESSAGE() as description
 END CATCH; 
 
 update [tbl_upload_bc25] set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_bc25_upload =@idfile
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description 
END 
--go
--BEGIN TRY
   
-- exec('[spi_getdata_bc25] @filename=''BC252017000072.csv'', @idfile=93349, @username=''admin'' ')

   
-- END TRY
-- BEGIN CATCH
-- SELECT
-- '06' AS status
-- ,ERROR_MESSAGE() AS description;
    
-- END CATCH;



GO