dbo.spi_mesin_in_out

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spi_mesin_in_out
(@filename varchar(100), @id_mesin_upload bigint, @tipe varchar(50),  @username varchar(100) )
AS
BEGIN
    --
  SET ANSI_WARNINGS ON
 SET ANSI_NULLS ON
 IF @tipe='MPKIN'
    begin
    
    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+'\invoice\mesin_alat_ktr\mesin_alat_kantor_in'
 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_data_mesin_in_out where id_data_mesin=@id_mesin_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 +
''', [''DATA MPK IN$'']) WHERE F1=''KODE_BARANG'' AND F2=''NAMA_BARANG'' and F3=''NO_DOK_IN'' and F4=''DATE_IN'' and f7=''query'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA MESIN DAN PERALATAN KANTOR IN TIDAK SESUAI'' AS description RETURN END if ( select count(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''DATA MPK IN$'']) where KODE_BARANG = '''' or KODE_BARANG is not null or NO_DOK_IN = '''' or NO_DOK_IN is not null ) = 0 begin SELECT ''06'' AS status, ''DATA TIDAK BISA DIPROSES'' AS description RETURN end Declare @tbl_dt_mpk_in table ( KODE_BARANG varchar(100) null, NAMA_BARANG varchar(100) null, NO_DOK_IN varchar(100) null, DATE_IN date null, QTY float null, UNIT varchar(100) null, query varchar(100) ); insert into @tbl_dt_mpk_in ( KODE_BARANG, NAMA_BARANG, NO_DOK_IN, DATE_IN, QTY, UNIT, query ) select KODE_BARANG, NAMA_BARANG, NO_DOK_IN, DATE_IN, QTY, UNIT, query FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpath +
''', [''DATA MPK IN$'']) where KODE_BARANG = '''' or KODE_BARANG is not null or NO_DOK_IN = '''' or NO_DOK_IN is not null delete from @tbl_dt_mpk_in where KODE_BARANG = '''' or KODE_BARANG is null or NO_DOK_IN = '''' or NO_DOK_IN is null declare @cnt2 int,@cnt3 int set @cnt2=(select count(*) from ( select KODE_BARANG from @tbl_dt_mpk_in except select kode_brg from dbo.tbl_dt_mesindanalatkantor_code ) x ) if @cnt2 > 0 begin set @cnt3=(select count(*) from @tbl_dt_mpk_in a inner join tbl_dt_mesindanalatkantor_code ON tbl_dt_mesindanalatkantor_code.kode_brg= a.KODE_BARANG) select ''07'' as status, ''ADA DATA TIDAK MATCH: ''+cast(@cnt2 as varchar)+'' MATCH: ''+ cast(@cnt3 as varchar) +'' Mohon dipriksa kembali datanya'' as description return end declare @jam date, @usernm varchar(100) set @jam = getdate() set @usernm = '''+ @username +
''' insert into tbl_dt_mpk_in ( [part_no] ,part_name ,[no_in] ,[date_in] ,[qty_in] ,unit_in ,create_by, update_by, create_date, update_date ,filename ) select KODE_BARANG ,NAMA_BARANG ,NO_DOK_IN ,DATE_IN ,QTY ,UNIT ,@usernm, @usernm, @jam, @jam ,''' + @filename+ 
''' from @tbl_dt_mpk_in --MERGE tbl_dt_mpk_in AS TARGET --USING (select a.KODE_BARANG, a.NAMA_BARANG, a.NO_DOK_IN,a.DATE_IN, sum(a.QTY) as QTY,a.UNIT from @tbl_dt_mpk_in a group by a.KODE_BARANG, a.NO_DOK_IN,a.DATE_IN,a.UNIT, a.NAMA_BARANG) AS SOURCE --ON ( replace(SOURCE.KODE_BARANG, char(32), '''') = replace(TARGET.part_no, char(32), '''') and TARGET.part_name = SOURCE.NAMA_BARANG and TARGET.no_in = SOURCE.NO_DOK_IN and TARGET.no_in = SOURCE.NO_DOK_IN ) --WHEN MATCHED AND TARGET.date_in = SOURCE.DATE_IN --THEN --UPDATE set --TARGET.part_no = SOURCE.KODE_BARANG, --TARGET.part_name = SOURCE.NAMA_BARANG, --TARGET.no_in = SOURCE.NO_DOK_IN, --TARGET.date_in = SOURCE.DATE_IN, --TARGET.qty_in = SOURCE.QTY, --TARGET.unit_in = SOURCE.UNIT --WHEN NOT MATCHED BY TARGET THEN --INSERT ( -- [part_no] -- ,part_name -- ,[no_in] -- ,[date_in] -- ,[qty_in] -- ,unit_in -- ,create_by, update_by, create_date, update_date) values --( -- KODE_BARANG, -- NAMA_BARANG, -- NO_DOK_IN, -- DATE_IN, -- QTY, -- UNIT -- ,'''+@username+''','''+@username+
''' ,@jam,@jam) --; delete FROM tbl_dt_mpk_in WHERE part_no IN ( select KODE_BARANG from @tbl_dt_mpk_in where query = ''delete'' ) and part_name in ( select NAMA_BARANG from @tbl_dt_mpk_in where query = ''delete'' ) and date_in in ( select DATE_IN from @tbl_dt_mpk_in where query = ''delete'' ) and no_in in ( select NO_DOK_IN from @tbl_dt_mpk_in where query = ''delete'' ) and qty_in in ( select QTY from @tbl_dt_mpk_in where query = ''delete'' ) and unit_in in ( select UNIT from @tbl_dt_mpk_in where query = ''delete'' ) ; ')
 
 
 update tbl_upload_data_mesin_in_out set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_data_mesin =@id_mesin_upload
 
 EXEC xp_cmdshell @cmdmove, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
    end
    
    IF @tipe='MPKOUT'
    begin
 
    Declare @dirout varchar(100)
 declare @filedirout varchar(200)
 declare @filecountout int
 declare @fullpathout varchar(200)
 DECLARE @Filesout table(Names varchar(250) null)
 declare @dircmdout varchar(500)
 declare @cmdmoveout varchar(500)
 set @dirout = (select top 1 direktori from dbo.tbl_direktori)
 set @filedirout=@dirout+'\invoice\mesin_alat_ktr\mesin_alat_kantor_out'
 set @fullpathout=@filedirout+'\'+@filename
 set @dircmdout='dir '+@fullpathout
 set @cmdmoveout = 'MOVE /Y '+ @filedirout +'\'+ @FileName + ' '+ @filedirout +'\log\' + @filename
 
 --
 INSERT INTO @Filesout EXEC MASTER..XP_CMDSHELL @dircmdout
 set @filecountout = (SELECT COUNT (*) from @Files where Names like '%File Not Found%')
 if @filecountout = 1
 begin
  select '06' as status, names+' 0 ' as description from @Filesout  where Names like '%File Not Found%'
  return
 end 
 
 if(select COUNT(*) from tbl_upload_data_mesin_in_out where id_data_mesin=@id_mesin_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='+@fullpathout +
''', [''DATA MPK OUT$'']) WHERE F1=''KODE_BARANG'' AND F2=''NAMA_BARANG'' and F3=''NO_DOK_OUT'' and F4=''DATE_OUT'' and f7=''query'') IF @cnt=0 BEGIN SELECT ''06'' AS status, ''FILE DATA MESIN DAN PERALATAN KANTOR OUT TIDAK SESUAI'' AS description RETURN END if ( select count(*) FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpathout +
''', [''DATA MPK OUT$'']) where KODE_BARANG = '''' or KODE_BARANG is not null or NO_DOK_OUT = '''' or NO_DOK_OUT is not null ) = 0 begin SELECT ''06'' AS status, ''DATA TIDAK BISA DIPROSES'' AS description RETURN end Declare @tbl_dt_mpk_OUT table ( KODE_BARANG varchar(100) null, NAMA_BARANG varchar(100) null, NO_DOK_OUT varchar(100) null, DATE_OUT date null, QTY float null, UNIT varchar(100) null, query varchar(100) ); insert into @tbl_dt_mpk_OUT ( KODE_BARANG, NAMA_BARANG, NO_DOK_OUT, DATE_OUT, QTY, UNIT, query ) select KODE_BARANG, NAMA_BARANG, NO_DOK_OUT, DATE_OUT, QTY, UNIT, query FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 8.0;HDR=YES;IMEX=1;Database='+@fullpathout +
''', [''DATA MPK OUT$'']) where KODE_BARANG = '''' or KODE_BARANG is not null or NO_DOK_OUT = '''' or NO_DOK_OUT is not null delete from @tbl_dt_mpk_OUT where KODE_BARANG = '''' or KODE_BARANG is null or NO_DOK_OUT = '''' or NO_DOK_OUT is null declare @cnt2 int,@cnt3 int set @cnt2=(select count(*) from ( select KODE_BARANG from @tbl_dt_mpk_OUT except select kode_brg from dbo.tbl_dt_mesindanalatkantor_code ) x ) if @cnt2 > 0 begin set @cnt3=(select count(*) from @tbl_dt_mpk_OUT a inner join tbl_dt_mesindanalatkantor_code ON tbl_dt_mesindanalatkantor_code.kode_brg= a.KODE_BARANG) select ''07'' as status, ''ADA DATA TIDAK MATCH: ''+cast(@cnt2 as varchar)+'' MATCH: ''+ cast(@cnt3 as varchar) +'' Mohon dipriksa kembali datanya'' as description return end declare @jam date, @usernm varchar(100) set @jam = getdate() set @usernm = '''+ @username +
''' insert into tbl_dt_mpk_OUT ( [part_no] ,part_name ,[no_out] ,[date_out] ,[qty_out] ,[unit_out] ,create_by, update_by, create_date, update_date ,filename ) select KODE_BARANG ,NAMA_BARANG ,NO_DOK_OUT ,DATE_OUT ,QTY ,UNIT ,@usernm, @usernm, @jam, @jam ,''' + @filename+ 
''' from @tbl_dt_mpk_OUT --MERGE tbl_dt_mpk_OUT AS TARGET --USING (select a.KODE_BARANG, a.NAMA_BARANG, a.NO_DOK_OUT,a.DATE_OUT, sum(a.QTY) as QTY,a.UNIT from @tbl_dt_mpk_OUT a group by a.KODE_BARANG, a.NO_DOK_OUT,a.DATE_OUT,a.UNIT, a.NAMA_BARANG) AS SOURCE --ON ( replace(SOURCE.KODE_BARANG, char(32), '''') = replace(TARGET.part_no, char(32), '''') and TARGET.part_name = SOURCE.NAMA_BARANG and TARGET.no_OUT = SOURCE.NO_DOK_OUT and TARGET.no_OUT = SOURCE.NO_DOK_OUT ) --WHEN MATCHED AND TARGET.date_OUT = SOURCE.DATE_OUT --THEN --UPDATE set --TARGET.part_no = SOURCE.KODE_BARANG, --TARGET.part_name = SOURCE.NAMA_BARANG, --TARGET.no_OUT = SOURCE.NO_DOK_OUT, --TARGET.date_OUT = SOURCE.DATE_OUT, --TARGET.qty_OUT = SOURCE.QTY, --TARGET.unit_OUT = SOURCE.UNIT --WHEN NOT MATCHED BY TARGET THEN --INSERT ( -- [part_no] -- ,part_name -- ,[no_OUT] -- ,[date_OUT] -- ,[qty_OUT] -- ,unit_OUT -- ,create_by, update_by, create_date, update_date) values --( -- KODE_BARANG, -- NAMA_BARANG, -- NO_DOK_OUT, -- DATE_OUT, -- QTY, -- UNIT -- ,'''+@username+''','''+@username+
''' ,@jam,@jam) --; delete FROM tbl_dt_mpk_OUT WHERE part_no IN ( select KODE_BARANG from @tbl_dt_mpk_OUT where query = ''delete'' ) and part_name in ( select NAMA_BARANG from @tbl_dt_mpk_OUT where query = ''delete'' ) and date_OUT in ( select DATE_OUT from @tbl_dt_mpk_OUT where query = ''delete'' ) and no_OUT in ( select NO_DOK_OUT from @tbl_dt_mpk_OUT where query = ''delete'' ) and qty_OUT in ( select QTY from @tbl_dt_mpk_OUT where query = ''delete'' ) and unit_OUT in ( select UNIT from @tbl_dt_mpk_OUT where query = ''delete'' ) ; ')
 
 
 update tbl_upload_data_mesin_in_out set status_upload='DATA PROCESSED' where [filename] = @FileName and [status_upload] = 'SUKSES' and id_data_mesin =@id_mesin_upload
 
 EXEC xp_cmdshell @cmdmoveout, NO_OUTPUT
 select '00' as status, 'DATA SUKSES PROCESSED' as description
    end
 
END
GO