dbo.MG_MS_MOLTS_FILE_PROSSES

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create procedure dbo.MG_MS_MOLTS_FILE_PROSSES
@PARAMETER xml='' 
---XencX---with encryption
as

SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED

declare @pemisah varchar(255)
declare @pemisah_replace varchar(255)
set @pemisah_replace='<br />'
--set @pemisah=CHAR(13) + CHAR(10) + '
'
set @pemisah = '
'

declare @resultX varchar(max)
declare @resultX2 varchar(max)

declare @XmlSts xml
declare @raw xml
declare @xStsnoheader varchar(2),
  @xStsno varchar(2),
  @xStsdes varchar(max)
   
set @xStsnoheader='07'
set @xStsno='00';
set @xStsdes='sukses'
 
BEGIN TRY
SET NOCOUNT ON
DECLARE @NOW datetime
set @NOW =GETDATE()
  

DECLARE 
@data_by varchar(50)
,@ms_file_id bigint 
 
SELECT 
 @data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50) 
,@ms_file_id=left(ltrim(rtrim(isnull(tempTable.item.value('ms_file_id[1]', 'bigint'),-1))),255)
FROM @PARAMETER.nodes('param/field')  tempTable(item);

declare
@c_ms_file_id bigint  
,@c_file_type_id int  
,@c_status_file_id int  
,@c_ms_file_h_status_id int  
,@c_file_name varchar(255)  
,@c_file_url varchar(500)  
,@c_status_file_name varchar(500) 
,@c_file_type_name varchar(500)  
,@c_default_folder varchar(500)  
,@c_temp_folder varchar(500)  
,@c_format_folder varchar(500)  
,@c_allow_ext varchar(500)  

SELECT TOP(1)        
 @c_ms_file_id=dt_ms_file.ms_file_id
, @c_file_type_id=dt_ms_file.file_type_id
, @c_status_file_id=dt_ms_file_h_status.status_file_id
, @c_ms_file_h_status_id=dt_ms_file.ms_file_h_status_id
, @c_file_name=dt_ms_file.file_name
, @c_file_url=dt_ms_file.file_url
, @c_status_file_name=enum_ms_status_file.status_file_name
, @c_file_type_name=enum_ms_file_type.file_type_name
, @c_default_folder=enum_ms_file_type.default_folder
, @c_format_folder=enum_ms_file_type.format_folder
, @c_temp_folder=enum_ms_file_type.temp_folder
, @c_allow_ext=enum_ms_file_type.allow_ext
FROM            dt_ms_file INNER JOIN
                         dt_ms_file_h_status ON dt_ms_file.ms_file_h_status_id = dt_ms_file_h_status.ms_file_h_status_id INNER JOIN
                         enum_ms_status_file ON dt_ms_file_h_status.status_file_id = enum_ms_status_file.status_file_id INNER JOIN
                         enum_ms_file_type ON dt_ms_file.file_type_id = enum_ms_file_type.file_type_id
WHERE        (dt_ms_file.ms_file_id = @ms_file_id)
 
 
 
if @c_ms_file_id is null
begin
 set @xStsno='01';
 set @xStsdes='Gagal, Data File tidak di temukan ';
 goto f  
end

if not(@c_file_type_id=1)
begin
 set @xStsno='03';
 set @xStsdes='Gagal, File ' + @c_file_name + ' bukan merupakan file ' + @c_file_type_name;
 goto f 

end 
if not(@c_status_file_id=0)
begin
 set @xStsno='02';
 set @xStsdes='Gagal, Status File sudah ' + @c_status_file_name ;
 goto f 

end  


--if (SELECT COUNT(*) AS Expr1
--FROM dt_sa_h_status INNER JOIN
-- dt_sa ON dt_sa_h_status.sa_h_status_id = dt_sa.sa_h_status_id
--WHERE (NOT (dt_sa_h_status.status_sa_id = - 1)) AND (dt_sa.sa_name = @c_file_name))>1
--begin
-- set @xStsno='01';
-- set @xStsdes='Gagal, Nama SA Sudah di gunakan ';
-- goto f
--end
 

  declare @SQLString nvarchar(MAX) 
 ,@ParmDefinition nvarchar(MAX)
 
declare @tmp_data as table(
 urut bigint identity(1,1)
,INVOICE_NO varchar(255)
,INVOICE_DATE varchar(255)
,FORWARDER varchar(255)
,CONSIGNEE varchar(255)
,BUYER  varchar(255)
,SHIPPING_MODE varchar(255)
,ETD varchar(255)
,ETA varchar(255)
,PAYMENT varchar(255)
,ORDER_NO varchar(255)
,ITEM_CODE varchar(255)
,ITEM_TYPE_1 varchar(255)
,ITEM_TYPE_2 varchar(255)
,DESCRIPTION_1 varchar(255)
,DESCRIPTION_2 varchar(255)
,Qty varchar(255)
,UNIT varchar(255)
,UNIT_PRICE varchar(255)
,TOTAL_PRICE varchar(255)
,CURRENCY varchar(255)
,NET_WEIGHT_UNIT varchar(255)
,NET_WEIGHT_TOTAL varchar(255)
,HS_CODE varchar(255)
,DANGER_GOODS varchar(255)
,REGULATION varchar(255)
,TIRE_1_SUPPLIER varchar(255)
,COUNTRY_OF_ORIGIN varchar(255)
,SHIPPER varchar(255)
,VESSEL_NAME varchar(255)
,INVOICE_TERM varchar(255) 
 ,ms_item_id bigint 
 ,ms_molts_invoice_id bigint
 )
 
 
declare @new_name_file varchar(500)=dbo.axCopyFileNoReplace(@c_file_url,@c_temp_folder)  
set @new_name_file=substring(@new_name_file,3,500)
declare  @resultsave varchar(max)=dbo.axRegexReplaceFileText(@new_name_file,',(?!(?:[^"]*"[^"]*")*[^"]*$)','-')
set  @resultsave  =dbo.axRegexReplaceFileText(@new_name_file,'[^\u0000-\u007F]+','')
    
  
declare @isiformat varchar(max)= dbo.formatxml(@new_name_file,@c_file_type_id,',')
declare @fileformat varchar(500)= @c_format_folder + '\' + convert(varchar(255),NEWID()) + '.xml'
declare @result_file varchar(max)=dbo.axWriteFileText(@isiformat,@fileformat,1)
   

set @SQLString=
' SELECT INVOICE_NO ,INVOICE_DATE ,FORWARDER ,CONSIGNEE ,BUYER ,SHIPPING_MODE ,ETD ,ETA ,PAYMENT ,ORDER_NO ,ITEM_CODE ,ITEM_TYPE_1 ,ITEM_TYPE_2 ,DESCRIPTION_1 ,DESCRIPTION_2 ,Qty ,LEFT(LTRIM(RTRIM(UNIT)),3) as UNIT ,UNIT_PRICE ,TOTAL_PRICE ,CURRENCY ,NET_WEIGHT_UNIT ,NET_WEIGHT_TOTAL ,LEFT(LTRIM(RTRIM(HS_CODE)),15) as HS_CODE ,DANGER_GOODS ,REGULATION ,TIRE_1_SUPPLIER ,COUNTRY_OF_ORIGIN ,SHIPPER ,VESSEL_NAME ,INVOICE_TERM FROM OPENROWSET( BULK ''' + @new_name_file + 
''' , FORMATFILE = '''+@fileformat+
''', FIRSTROW = 2, MAXERRORS = 0) as data WHERE (not (LTRIM(RTRIM(isnull(INVOICE_NO,'''')))='''')) ' 

SET @ParmDefinition = N'';

 


insert into @tmp_data (INVOICE_NO
,INVOICE_DATE
,FORWARDER
,CONSIGNEE
,BUYER 
,SHIPPING_MODE
,ETD
,ETA
,PAYMENT
,ORDER_NO
,ITEM_CODE
,ITEM_TYPE_1
,ITEM_TYPE_2
,DESCRIPTION_1
,DESCRIPTION_2
,Qty
,UNIT
,UNIT_PRICE
,TOTAL_PRICE
,CURRENCY
,NET_WEIGHT_UNIT
,NET_WEIGHT_TOTAL
,HS_CODE
,DANGER_GOODS
,REGULATION
,TIRE_1_SUPPLIER
,COUNTRY_OF_ORIGIN
,SHIPPER
,VESSEL_NAME
,INVOICE_TERM 
 )
EXECUTE sp_executesql  @SQLString, @ParmDefinition  

set @result_file=dbo.axFileDelete(@new_name_file)
 
SET @resultX= (

SELECT       
  'NO : '+ INVOICE_NO +
 +' DATE : ' +  dbo.date_to_str(cast(INVOICE_DATE as date))
+ @pemisah 
AS [text()] 
FROM  ( 
  select INVOICE_NO
  ,cast(INVOICE_DATE as date) INVOICE_DATE
  ,FORWARDER  
  ,SHIPPING_MODE
  ,cast(ETD as date)ETD
  ,cast(ETA as date)ETA  
  ,SHIPPER
  ,CURRENCY
  ,VESSEL_NAME
  ,INVOICE_TERM  from @tmp_data
  group by INVOICE_NO
  ,cast(INVOICE_DATE as date) 
  ,FORWARDER  
  ,SHIPPING_MODE
  ,cast(ETD as date)
  ,cast(ETA as date)  
  ,SHIPPER
  ,CURRENCY
  ,VESSEL_NAME
  ,INVOICE_TERM
)xinvoce
where exists(
select * from dt_ms_molts_invoice as xfilter
where xinvoce.INVOICE_NO=xfilter.invoice_no and xinvoce.INVOICE_DATE=xfilter.invoice_date
) 
order by INVOICE_DATE,INVOICE_NO
For XML PATH (''))     
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN 
   SET @xStsno='17';
   SET @xStsdes='Gagal , Data invoice sudah ada dalam Molts Invoce :' + @pemisah+  @pemisah+ @resultX + @pemisah+  @pemisah+ ' Periksa kembali data Molts ' ;
  GOto f
end    

MERGE @tmp_data AS target  
    USING dt_ms_item AS source
    ON (ltrim(rtrim(target.DESCRIPTION_1)) = source.part_no)  
    WHEN MATCHED THEN   
        UPDATE SET target.ms_item_id = source.ms_item_id  ;  
  

declare @tmp_invoice table (
 ms_molts_invoice_id bigint primary key
,INVOICE_NO varchar(255)
,INVOICE_DATE date
,ETD date
,ETA date
,SHIPPING_MODE varchar(255)
,FORWARDER varchar(255) 
,INVOICE_TERM varchar(255) 
,VESSEL_NAME varchar(255)
,SHIPPER varchar(255)
,CURRENCY varchar(255)
);
 
  
declare @ms_file_h_status_id bigint

BEGIN TRANSACTION;   
 
INSERT INTO dt_ms_molts_invoice
                         (ms_file_id
       , invoice_no
       , invoice_date
       , etd
       , eta
       , shipping_mode
       , forwarder
       , type_term
       , vessel
       , shipper
       , valuta_code
       , mode_source_out
       , source_out_id 
       , create_date
       , create_by
       )
output inserted.ms_molts_invoice_id
      ,inserted.invoice_no
       , inserted.invoice_date
       , inserted.etd
       , inserted.eta
       , inserted.shipping_mode
       , inserted.forwarder
       , inserted.type_term
       , inserted.vessel
       , inserted.shipper
       , inserted.valuta_code into @tmp_invoice
select    @ms_file_id
,     INVOICE_NO
 ,cast(INVOICE_DATE as date)  
,cast(ETD as date)
,cast(ETA as date)  
,SHIPPING_MODE 
,FORWARDER 
,INVOICE_TERM 
,VESSEL_NAME
,SHIPPER 
,CURRENCY
, - 1, - 1,@now,@data_by
from @tmp_data
group by  INVOICE_NO
 ,cast(INVOICE_DATE as date)  
,cast(ETD as date)
,cast(ETA as date)  
,SHIPPING_MODE 
,FORWARDER 
,INVOICE_TERM 
,VESSEL_NAME
,SHIPPER
,CURRENCY

MERGE @tmp_data AS target  
    USING @tmp_invoice AS source
    ON (target.INVOICE_NO = source.INVOICE_NO
 and  cast(target.INVOICE_DATE as date)  = source.INVOICE_DATE
 and cast(target.ETD as date)= source.ETD
 and cast(target.ETA as date) = source.ETA
 and target.CURRENCY = source.CURRENCY
 and target.SHIPPING_MODE = source.SHIPPING_MODE
 and target.FORWARDER = source.FORWARDER
 and target.INVOICE_TERM = source.INVOICE_TERM
 and target.VESSEL_NAME = source.VESSEL_NAME
 and target.SHIPPER = source.SHIPPER
 )  
    WHEN MATCHED THEN   
        UPDATE SET target.ms_molts_invoice_id = source.ms_molts_invoice_id  ;  
  

INSERT INTO dt_ms_molts_invoice_detail
                         (ms_molts_invoice_id
, ms_tpb_in_barang_id
, order_no
, item_code
, item_type_1
, item_type_2
, description_1
, description_2
, quantity
, unit
, price_unit
, price_total 
, weight_unit
, weight_total
, country_of_origin
, tier_1_supplier
, hs_code
, mapping_ms_item_id
, mapping_date
, mapping_by
, create_date
, create_by)
select         ms_molts_invoice_id
, -1
, order_no
, item_code
, item_type_1
, item_type_2
, description_1
, description_2
, Qty
, unit
, UNIT_PRICE
, TOTAL_PRICE 
, NET_WEIGHT_UNIT
, NET_WEIGHT_TOTAL
, country_of_origin
, TIRE_1_SUPPLIER
, replace(HS_CODE,'"','')
, ms_item_id
,case when ms_item_id is null then null else @now end -- mapping_date
,case when ms_item_id is null then null  else @data_by end -- mapping_by
,@now,@data_by
from @tmp_data 

INSERT INTO dt_ms_file_h_status
                         (ms_file_id, status_file_id, status_info, create_date, create_by)
VALUES        (@ms_file_id, 1, '',@now,@data_by)
set @ms_file_h_status_id=SCOPE_IDENTITY(); 

UPDATE       dt_ms_file
SET                ms_file_h_status_id = @ms_file_h_status_id
WHERE        (ms_file_id = @ms_file_id) 



  
declare @cur_ID bigint ,@cur_SHIPPER varchar(255) 
 
declare  @new_ms_supplier_customer_id bigint
  ,@new_map_type_ms_supplier_customer_id int


DECLARE map_cursor_molts_prosses CURSOR FOR  
select ms_molts_invoice_id ,  SHIPPER
from @tmp_invoice   
OPEN map_cursor_molts_prosses  
FETCH NEXT FROM map_cursor_molts_prosses INTO @cur_ID,@cur_SHIPPER
WHILE @@FETCH_STATUS = 0  
BEGIN   

  set @new_ms_supplier_customer_id=null 

  --CHECK BERDASARKAN KODE DAN NAMA

  select top (1)
  @new_ms_supplier_customer_id=ms_supplier_customer_id  
  from dt_ms_supplier_customer 
  where ltrim(rtrim(supplier_customer_name))=@cur_SHIPPER
     
  update dt_ms_molts_invoice
  set mapping_supplier_customer_id =@new_ms_supplier_customer_id 
  , mapping_date=@NOW 
  , mapping_by =@data_by 
  where ms_molts_invoice_id =@cur_ID

    FETCH NEXT FROM map_cursor_molts_prosses INTO @cur_ID,@cur_SHIPPER
    END  

CLOSE map_cursor_molts_prosses  
DEALLOCATE map_cursor_molts_prosses



COMMIT TRANSACTION;
  --ROLLBACK TRAN;
f:

set nocount off

END TRY  
BEGIN CATCH
 IF (@@TRANCOUNT > 0)  ROLLBACK TRAN;
 
 set @xStsno='88'
 set @xStsdes= ERROR_MESSAGE() 
END CATCH

g:
set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
 select  CAST( (select @XmlSts,isnull(@raw ,'')   for xml path ('result')
)  as XML)  as dt

GO