dbo.spi_bahanpenolong_out_ct_1

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create PROCEDURE dbo.spi_bahanpenolong_out_ct_1
(@filename varchar(100), @id_uploadbp_out_upload bigint, @tipe varchar(50),  @username varchar(100) )
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+'\inventory\bahan_penolong_out_ct'
 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_uploadbahanpenolong_out where id_uploadbp_out_upload=@id_uploadbp_out_upload 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 +
''', [''CT 01$'']) WHERE F1=''invoice_number'' AND F2=''container_number'' and F3=''description'' and F4=''Qty'' and F5=''UOM'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA BAHAN PENOLONG CT 01 TIDAK SESUAI'' AS description RETURN END Declare @tbl_bahan_penolong_out table ( invoice_number varchar(100) null, tanggal_invoice date null, container_number varchar(100) null, part_number varchar(100) null, description varchar(100) null, qty float null, qty_out float null, UOM varchar(100) null, doc_no varchar(100) null, doc_date date null, car varchar(100) null, filename varchar(200) null, doc_out_to int null ); insert into @tbl_bahan_penolong_out ( invoice_number, --tanggal_invoice, container_number, --part_number, description, qty, qty_out, UOM, --doc_no, --doc_date, --car, filename, doc_out_to ) select invoice_number, --tanggal_invoice, container_number, --part_number, description, qty, qty, UOM, --doc_no, --doc_date, --car, ''' + @fullpath + 
''', 1 FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''CT 01$'']) delete from @tbl_bahan_penolong_out where invoice_number = '''' or invoice_number is null --select a.description, b.part_name,b.part_no from @tbl_bahan_penolong_out a --left outer join [tbl_data_bahan_penolong] b on a.description = b.part_name if (select count(*) from @tbl_bahan_penolong_out a left outer join [tbl_data_bahan_penolong] b on a.description = b.part_name where b.part_name is null) > 0 begin select ''06 '' as status, '' ADA DATA PART DESCRIPTION TIDAK DITEMUKAN '' as description return end declare @invoice_number varchar(100), @car varchar(100), @nodaft varchar(100), @tgdaft date, @tanggal_invoice date declare @nobc_null varchar(max), @partno varchar(100), @description varchar(100), @part_nox varchar(100) set @nobc_null = '''' DECLARE db_cursor CURSOR FOR select a.invoice_number, a.[description] , b.part_no from @tbl_bahan_penolong_out a left outer join [tbl_data_bahan_penolong] b on a.description = b.part_name --group by invoice_number, [description] OPEN db_cursor FETCH NEXT FROM db_cursor INTO @invoice_number, @description , @part_nox WHILE @@FETCH_STATUS = 0 BEGIN set @partno = (select top 1 part_no from tbl_data_bahan_penolong where part_name = @description) if ( select count(*) from tbl_bc_out_hdr inner join tbl_bc_out_dok on tbl_bc_out_dok.id_bc_out_hdr = tbl_bc_out_hdr.id_bc_out_hdr where NoDok = @invoice_number and kddok = ''380'' ) > 0 begin select top 1 @car=tbl_bc_out_hdr.car, @nodaft=tbl_bc_out_hdr.nodaft, @tgdaft=tbl_bc_out_hdr.tgdaft, @tanggal_invoice=tbl_bc_out_dok.TgDok from tbl_bc_out_hdr inner join tbl_bc_out_dok on tbl_bc_out_dok.id_bc_out_hdr = tbl_bc_out_hdr.id_bc_out_hdr where NoDok = @invoice_number and kddok = ''380'' order by tbl_bc_out_dok.CAR desc update @tbl_bahan_penolong_out set doc_no=@nodaft, doc_date=@tgdaft, car=@car, tanggal_invoice=@tanggal_invoice, part_number=@part_nox where invoice_number=@invoice_number and [description] = @description end else if ( select count(*) from tbl_bc_out_hdr inner join tbl_bc_out_dok on tbl_bc_out_dok.id_bc_out_hdr = tbl_bc_out_hdr.id_bc_out_hdr where NoDok = @invoice_number and kddok = ''380'' ) = 0 begin set @nobc_null = @nobc_null + @invoice_number + ''#'' end FETCH NEXT FROM db_cursor INTO @invoice_number, @description , @part_nox END CLOSE db_cursor DEALLOCATE db_cursor if (select count(*) from @tbl_bahan_penolong_out where doc_no is null) > 0 begin select ''06 '' as status, '' ADA DATA BELUM ADA NO BC NYA '' + @nobc_null as description return end if (select count(*) from @tbl_bahan_penolong_out where part_number is null) > 0 begin select ''06 '' as status, '' ADA DATA PART nya TIDAK DITEMUKAN '' as description return end declare @jam date set @jam = getdate() MERGE tbl_bahanpenolong_out AS TARGET USING (select invoice_number, tanggal_invoice, container_number, part_number, description, sum(qty) as qty, sum(qty_out) as qty_out, UOM, doc_no, doc_date, filename, car, doc_out_to from @tbl_bahan_penolong_out group by invoice_number, tanggal_invoice, container_number, part_number, description, UOM, doc_no, doc_date, filename, car, doc_out_to ) AS SOURCE ON SOURCE.invoice_number = TARGET.no_invoice --and TARGET.container_number = SOURCE.container_number and SOURCE.description = TARGET.part_name and SOURCE.doc_out_to = TARGET.doc_out_to WHEN MATCHED THEN UPDATE set --TARGET.container_number = SOURCE.container_number, --TARGET.part_no = SOURCE.part_number, --TARGET.part_name = SOURCE.description, --TARGET.uom = SOURCE.uom, TARGET.qty = SOURCE.qty, TARGET.qty_out = SOURCE.qty_out --TARGET.doc_no = SOURCE.doc_no, --TARGET.doc_date = SOURCE.doc_date, --TARGET.car = SOURCE.car, --TARGET.filename = SOURCE.filename WHEN NOT MATCHED BY TARGET THEN INSERT ( no_invoice, tgl_invoice, container_number, part_no, part_name, qty, qty_out, uom, doc_no, doc_date, doc_out_to, approved_date, status_data, filename, car ,create_by, update_by, create_date, update_date) values ( invoice_number, tanggal_invoice, container_number, part_number, description, qty, qty_out, UOM, doc_no, doc_date, 1, doc_date, ''APPROVED'', filename, car, '''+@username+''','''+@username+
''' ,@jam,@jam) ; ')
 
 
 update tbl_uploadbahanpenolong_out set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_uploadbp_out_upload =@id_uploadbp_out_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
 
END
GO