dbo.spuload_coo_sync

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spuload_coo_sync
(@filepath varchar(500), @filename varchar(100), @username varchar(50), @tipe varchar(50), @trx varchar(50), @id bigint )
AS
BEGIN
 SET ANSI_NULLS ON
 SET ANSI_WARNINGS ON
 declare @str varchar(max)
 declare @tbl_coo table(
 VENDOR varchar(50) NULL,
 INVOICE_NO varchar(51) NULL,
 CONTIANER_NO varchar(50) NULL,
 INVOICE_DATE varchar(50) NULL,
 ETD_VEBDER varchar(50) NULL,
 ETD_PORT varchar(50) NULL,
 ETA_PORT varchar(50) NULL,
 ETA_FACT varchar(50) NULL,
 TRANSPORT_WAY varchar(50) NULL,
 CARA_BAYAR varchar(50) NULL,
 ONEROUS varchar(50) NULL,
 SHIP_FROM varchar(50) NULL,
 VIA varchar(50) NULL,
 SHIP_TO varchar(50) NULL,
 PAY_BY varchar(50) NULL,
 SEAL_NO varchar(50) NULL,
 SHIP_NAME varchar(50) NULL,
 SHIP_COMPANY varchar(50) NULL,
 ORDER_NO varchar(50) NULL,
 ITEM_NUMBER varchar(50) NULL,
 DESCRIPTION varchar(150) NULL,
 ARRIV_PLAN_NUMBER varchar(50) NULL,
 UOM varchar(50) NULL,
 PRICE varchar(50) NULL,
 CURRENT_MONEY varchar(50) NULL,
 CTN_NO_PREFIX varchar(50) NULL,
 CTN_NO_FROM varchar(50) NULL,
 CTN_NO_TO varchar(50) NULL,
 CTN_QUANTITY varchar(50) NULL,
 PACKING_QTY varchar(50) NULL,
 NET_WEIGHT varchar(50) NULL,
 GROSS_WEIGHT varchar(50) NULL,
 WEIGHT_UOM varchar(50) NULL,
 VOLUME varchar(50) NULL,
 VOLUME_UOM varchar(50) NULL,
 BL_DATE varchar(50) NULL,
 BL_NO varchar(50) NULL,
 FREIGHT varchar(50) NULL,
 EURO_NO varchar(50) NULL,
 PALET_NO varchar(50) NULL,
 HS_CODE varchar(50) NULL,
 COUNTRY_OF varchar(50) NULL,
 CAUPRI decimal(18,2),
 CATAMZ decimal(18,2),
 CAROUD varchar(5),
 AKHIR varchar(50) NULL
  --create_by varchar(30)
  --create_date datetime
  --status_inv int
  --car varchar(30)
  --nobc varchar(10)
  --tglbc datetime
  --nobl varchar(50)
  --tgbl date
 


 )
 

  if @trx = 'add' 
  begin
  BEGIN TRY
 
  set @str = 
' select * FROM OPENROWSET( ''Microsoft.ACE.OLEDB.12.0'' ,''Text;Database='+@filepath+
';HDR=NO;IMEX=1 ;FMT=Delimited;'', ''SELECT * FROM ['+@filename+
']'') where F2 <> ''INVOICE_NO'' ' 
   
 insert into @tbl_coo
 exec(@str)

 declare @VENDORX varchar(50)
 declare @vendorxx varchar(max)
 set @vendorxx = ''
 DECLARE tbltemp_data_coo_cursorx CURSOR FOR
 select VENDOR from @tbl_coo group by VENDOR
 OPEN tbltemp_data_coo_cursorx
 FETCH NEXT FROM tbltemp_data_coo_cursorx into @VENDORX
 WHILE @@FETCH_STATUS = 0
  BEGIN
   if (select count(*) from dbo.tbl_data_supplier where subcode_as400 = @VENDORX) = 0
   begin
    set @vendorxx = @vendorxx + @VENDORX + '#'
   end
   FETCH NEXT FROM tbltemp_data_coo_cursorx into @VENDORX
  END
 CLOSE tbltemp_data_coo_cursorx
 DEALLOCATE tbltemp_data_coo_cursorx
 if dbo.trim(@vendorxx) <> ''
 begin
  
  SELECT 'error' AS status, @vendorxx + ' VENDOR TIDAK DITEMUKAN'  AS description
  return

 end 

 declare @id_coo_data bigint
 declare @VENDOR varchar(50) , @INVOICE_NO varchar(51) , @INVOICE_DATE varchar(50), @cnt int, @SHIP_COMPANY varchar(50) 
 DECLARE tbltemp_data_coo_cursor CURSOR FOR
  select VENDOR, INVOICE_NO, INVOICE_DATE,count(INVOICE_NO) as cnt, SHIP_COMPANY from @tbl_coo
  group by vendor, invoice_no, invoice_date, SHIP_COMPANY
  OPEN tbltemp_data_coo_cursor  
  FETCH NEXT FROM tbltemp_data_coo_cursor into @VENDOR, @INVOICE_NO, @INVOICE_DATE, @cnt, @SHIP_COMPANY
  WHILE @@FETCH_STATUS = 0
  BEGIN
  

  if (select count(*) from tbl_coo_data where vendor = @VENDOR and no_invoice = @INVOICE_NO and tgl_invoice = @INVOICE_DATE) = 0
   BEGIN
    insert into [tbl_coo_data] ([vendor],[no_invoice], [tgl_invoice], SHIP_COMPANY ,[item_count], [file_name],[status_inv],[upload_by],[upload_date]) 
    values (@VENDOR,@INVOICE_NO,@INVOICE_DATE, @SHIP_COMPANY, @cnt, @filepath+'\'+@filename ,'NEW',@username,GETDATE())
    set @id_coo_data = scope_identity()

    insert into [tbl_coo_data_dtl] (
     [id_coo_data]
      ,[VENDOR]
      ,[INVOICE_NO]
      ,[CONTIANER_NO]
      ,[INVOICE_DATE]
      ,[ETD_VEBDER]
      ,[ETD_PORT]
      ,[ETA_PORT]
      ,[ETA_FACT]
      ,[TRANSPORT_WAY]
      ,[CARA_BAYAR]
      ,[ONEROUS]
      ,[SHIP_FROM]
      ,[VIA]
      ,[SHIP_TO]
      ,[PAY_BY]
      ,[SEAL_NO]
      ,[SHIP_NAME]
      ,[SHIP_COMPANY]
      ,[ORDER_NO]
      ,[ITEM_NUMBER]
      ,[DESCRIPTION]
      ,[ARRIV_PLAN_NUMBER]
      ,[UOM]
      ,[PRICE]
      ,[CURRENT_MONEY]
      ,[CTN_NO_PREFIX]
      ,[CTN_NO_FROM]
      ,[CTN_NO_TO]
      ,[CTN_QUANTITY]
      ,[PACKING_QTY]
      ,[NET_WEIGHT]
      ,[GROSS_WEIGHT]
      ,[WEIGHT_UOM]
      ,[VOLUME]
      ,[VOLUME_UOM]
      ,[BL_DATE]
      ,[BL_NO]
      ,[FREIGHT]
      ,[EURO_NO]
      ,[PALET_NO]
      ,[HS_CODE]
      ,[COUNTRY_OF]
      , CAUPRI
     ,CATAMZ 
    , CAROUD
      ,[AKHIR]
      ,[create_by]
      ,[create_date]
      ,[status_inv]
   
    ) select 
    @id_coo_data,
    VENDOR ,
    INVOICE_NO ,
    CONTIANER_NO ,
    INVOICE_DATE ,
    ETD_VEBDER ,
    ETD_PORT ,
    ETA_PORT ,
    ETA_FACT ,
    TRANSPORT_WAY ,
    CARA_BAYAR ,
    ONEROUS ,
    SHIP_FROM ,
    VIA ,
    SHIP_TO ,
    PAY_BY ,
    SEAL_NO ,
    SHIP_NAME ,
    SHIP_COMPANY ,
    ORDER_NO ,
    ITEM_NUMBER ,
    DESCRIPTION ,
    ARRIV_PLAN_NUMBER ,
    UOM ,
    PRICE ,
    CURRENT_MONEY ,
    CTN_NO_PREFIX ,
    CTN_NO_FROM ,
    CTN_NO_TO ,
    CTN_QUANTITY ,
    PACKING_QTY ,
    NET_WEIGHT ,
    GROSS_WEIGHT ,
    WEIGHT_UOM ,
    VOLUME ,
    VOLUME_UOM ,
    BL_DATE ,
    BL_NO ,
    FREIGHT ,
    EURO_NO ,
    PALET_NO ,
    HS_CODE ,
    COUNTRY_OF ,
      CAUPRI,
    CATAMZ ,
     CAROUD,
    AKHIR,
    @username,
    GETDATE(),
    'NEW' 

    from @tbl_coo
    where VENDOR=@VENDOR and INVOICE_NO = @INVOICE_NO and INVOICE_DATE = @INVOICE_DATE
    END 
   FETCH NEXT FROM tbltemp_data_coo_cursor into @VENDOR, @INVOICE_NO, @INVOICE_DATE, @cnt, @SHIP_COMPANY
  END
  CLOSE tbltemp_data_coo_cursor
  DEALLOCATE tbltemp_data_coo_cursor

  insert into tbl_upload_coo(
   filename    ,
   filepath  ,
   tipe,
   status_upload,
   date_upload,
   create_by,
   create_date,
   update_by,
   update_date
  ) select
  @filename
  , @filepath +'\'+ @filename
  ,'coo'
  ,'sukses'
  ,GETDATE()
  ,@username
  ,GETDATE()
  ,@username
  ,GETDATE()

  declare @resx varchar(70)
  set @resx=(select [dbo].[axFileDelete](@filepath+'\'+@filename))
  SELECT '00' AS status, 'DATA SUKSES' AS description
   END TRY 
 BEGIN CATCH
  SELECT 
  'error' AS status
  ,ERROR_MESSAGE() AS description;
   
 END CATCH; 
 
   
 
  end
  if @trx = 'delete' 
  begin
 declare @resx1 varchar(70)
 set @resx1=(select [dbo].[axFileDelete](@filepath+'\'+@filename))
 SELECT '00' AS status, 'DATA DELETED' AS description
  end
  


 
END
--go
--exec [spuload_coo_sync] 'D:\pemi\coo','INV COO SAMPLE NEW 1.CSV','admin','coo','add',-3
GO