dbo.MG_MS_MANUAL_INVOICE_FILE_PROSSES

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create procedure dbo.MG_MS_MANUAL_INVOICE_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 
,@file_type_id bigint 
, @ms_manual_invoice_type_id bigint
SELECT 
 @data_by=left(ltrim(rtrim(isnull(tempTable.item.value('data_by[1]', 'varchar(100)'),''))),50) 
,@ms_file_id=isnull(tempTable.item.value('ms_file_id[1]', 'bigint'),-1)
,@file_type_id=isnull(tempTable.item.value('file_type_id[1]', 'bigint'),-1)
,@ms_manual_invoice_type_id=isnull(tempTable.item.value('ms_manual_invoice_type_id[1]', 'bigint'),-1)
FROM @PARAMETER.nodes('param/field')  tempTable(item);
 
if @ms_file_id=-1
begin
 set @xStsno='01';
 set @xStsdes='Gagal, Data File tidak di temukan(1) ';
 goto f  
end


if @ms_manual_invoice_type_id =-1
begin
 set @xStsno='01';
 set @xStsdes='Gagal, Data File tidak di temukan(2) ';
 goto f  
end


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)    
,@c_default_sheet 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
,@c_default_sheet=        enum_ms_file_type.default_sheet
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)

--enum_ms_file_type
--2 INV 23 KURIR D:\TempFileData\PEMI\KURIR23 D:\TempFileData\PEMI\KURIR23\tmp D:\TempFileData\PEMI\KURIR23\frm xls,xlsx INVOICE$
--3 INV 40 D:\TempFileData\PEMI\MANUAL40 D:\TempFileData\PEMI\MANUAL40\tmp D:\TempFileData\PEMI\MANUAL40\frm xls,xlsx INVOICE$
--4 INV 27 IN D:\TempFileData\PEMI\MANUAL271 D:\TempFileData\PEMI\MANUAL271\tmp D:\TempFileData\PEMI\MANUAL271\frm xls,xlsx INVOICE$
 
-- enum_ms_manual_invoice_type
-- 1 BC 23 KURIR
--2 BC 40
--3 BC 27 IN
 
if @c_ms_file_id is null
begin
 set @xStsno='01';
 set @xStsdes='Gagal, Data File tidak di temukan(3) ';
 goto f  
end


if not(@c_file_type_id=  @ms_manual_invoice_type_id+1 )
begin
 set @xStsno='01';
 set @xStsdes='Gagal, Data File tidak di temukan(4) ';
 goto f  
end

if not(@c_file_type_id=  @file_type_id )
begin
 set @xStsno='01';
 set @xStsdes='Gagal, Data File tidak di temukan(5) ';
 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  


declare @file_type_name varchar(50)  
,@default_folder varchar(500)  
,@allow_ext varchar(100)  


SELECT        
@file_type_name = file_type_name,
 @default_folder=default_folder,
  @allow_ext=allow_ext
FROM            enum_ms_file_type
WHERE        (file_type_id = @file_type_id) 


if @file_type_name is null
begin
 set @xStsno='02';
 set @xStsdes='Gagal,Error Check File' ;
 goto f 

end  


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

end 
if  dbo.axSheetExists( @c_file_url,@c_default_sheet)=0
begin 
select top(1) @c_default_sheet=sheet_name from   dbo.axSheetList(  @c_file_url ) as x where sheet_index=0
end
 

 declare @tmp_data table(
 row_num bigint identity(1,1)
 ,part_name varchar(255)
 ,kode_barang varchar(255)
 ,part_no varchar(255)
 ,kode_supplier varchar(255)
 ,maker varchar(255)
 ,order_qty varchar(255)
 ,order_no varchar(255)
 ,order_date varchar(255)
 ,req_eta_date varchar(255)
 ,shipping_by varchar(255)
 ,invoice_no varchar(255)
 ,invoice_date varchar(255)
 ,inv_qty varchar(255)
 ,unit varchar(255)
 ,rec_date varchar(255)
 ,rec_qty varchar(255)
 ,istatus varchar(255)
 ,price varchar(255)
 ,amount varchar(255)
 ,remark varchar(255) 
 ,netto varchar(255) 
 ,bruto varchar(255) 
    ,ms_item_id bigint 
 ,ms_manual_invoice_id bigint  
)
 


declare @tp_ms_xls_check_column tp_ms_xls_check_column 
insert into @tp_ms_xls_check_column 
(source_ordering
,source_col
,source_type_id 
,source_type_name
,xls_column 
,xls_type
,xls_type_name
,ordinal_position)
SELECT         enum_ms_file_type_field.ordering, enum_ms_file_type_field.source_col, enum_ms_file_type_field.source_type_id, enum_ms_col_xls_type_setting.col_xls_type_name AS source_type_name, source_data.column_name AS xls_column, source_data.xls_type, 
                         enum_ms_col_xls_type_source.col_xls_type_name AS xls_type_name, source_data.ordinal_position
FROM            dbo.axColumnListBySheetName(@c_file_url, @c_default_sheet) AS source_data LEFT OUTER JOIN
                         enum_ms_col_xls_type AS enum_ms_col_xls_type_source ON source_data.xls_type = enum_ms_col_xls_type_source.col_xls_type_id RIGHT OUTER JOIN
                         enum_ms_col_xls_type AS enum_ms_col_xls_type_setting RIGHT OUTER JOIN
                         enum_ms_file_type_field ON enum_ms_col_xls_type_setting.col_xls_type_id = enum_ms_file_type_field.source_type_id ON source_data.column_name = enum_ms_file_type_field.source_col
WHERE        (enum_ms_file_type_field.file_type_id = @file_type_id)
ORDER BY enum_ms_file_type_field.ordering


--select * from @tp_ms_xls_check_column
--select * from dbo.axSheetList( @filename )
--select * from dbo.axColumnListBySheetName(@c_file_url,@c_default_sheet)
--select * from dbo.axSheetList( @filename )

declare  @data_file xml=dbo.axSheetData(@c_file_url,'select * from ['+ @c_default_sheet + '] where len(part_no)>0','')
 

 insert into @tmp_data (
 part_name
,kode_barang
,part_no
,kode_supplier
,maker
,order_qty
,order_no
,order_date
,req_eta_date 
,shipping_by
,invoice_no
,invoice_date
,inv_qty
,unit
,rec_date
,rec_qty
,istatus
,price
,amount
,remark
,netto 
,bruto )
SELECT 
 tempTable.item.value('part_name[1]', 'varchar(255)')
,tempTable.item.value('kode_barang[1]', 'varchar(255)') 
,tempTable.item.value('part_no[1]', 'varchar(255)') 
,tempTable.item.value('kode_supplier[1]', 'varchar(255)') 
,tempTable.item.value('maker[1]', 'varchar(255)') 
,tempTable.item.value('order_qty[1]', 'varchar(255)') 
,tempTable.item.value('order_no[1]', 'varchar(255)') 
,tempTable.item.value('order_date[1]', 'varchar(10)') 
,tempTable.item.value('req_eta_date[1]', 'varchar(10)') 
,tempTable.item.value('shipping_by[1]', 'varchar(255)') 
,tempTable.item.value('invoice_no[1]', 'varchar(255)') 
,tempTable.item.value('invoice_date[1]', 'varchar(10)') 
,tempTable.item.value('inv_qty[1]', 'varchar(255)') 
,tempTable.item.value('unit[1]', 'varchar(255)') 
,tempTable.item.value('rec_date[1]', 'varchar(10)') 
,tempTable.item.value('rec_qty[1]', 'varchar(255)') 
,tempTable.item.value('status[1]', 'varchar(255)') 
,tempTable.item.value('price[1]', 'varchar(255)') 
,tempTable.item.value('amount[1]', 'varchar(255)') 
,tempTable.item.value('remark[1]', 'varchar(255)')  
,tempTable.item.value('netto[1]', 'varchar(255)')  
,tempTable.item.value('bruto[1]', 'varchar(255)')  
FROM @data_file.nodes('root/row')  tempTable(item);


--select * from @tmp_data
    
--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 @result_file=dbo.axFileDelete(@new_name_file)

SET @resultX= ( 
SELECT       
  'row : '+ cast(row_num + 1 as varchar(255))+ 
 +' ,Invoice No: ' +  invoice_no
 +' ,Invoice Date: ' +  isnull(dbo.date_to_str(invoice_date),'')
+ @pemisah 
AS [text()] 
FROM   @tmp_data as xfilter 
 where nullif(ltrim(rtrim(invoice_date)),'') is null or ISDATE(invoice_date)=0
order by  row_num 
For XML PATH (''))     
SET @resultX= REPLACE(@resultX,@pemisah_replace,@pemisah);
IF @resultX is not null
BEGIN 
   SET @xStsno='17';
   SET @xStsdes='Gagal , Ada Tanggal Invoice yang kosong:' + @pemisah+  @pemisah+ @resultX + @pemisah+  @pemisah+ 'Isi Tanggal Invoice ' ;
  GOto f
end    


set @resultX=null;
SET @resultX= ( 
SELECT       
  'No. : '+ invoice_no +
 +' , Date : ' +  dbo.date_to_str(cast(invoice_date as date))
 +' , Type : ' +  type_invoice
+ @pemisah 
AS [text()] 
FROM  vw_ms_data_all_invoice as xInvoice
where exists( 
  select 
  invoice_no
  ,cast(invoice_date as date) invoice_date
  ,cast(req_eta_date as date) req_eta_date
  ,kode_supplier  
  ,shipping_by  
  ,cast(rec_date as date) rec_date
  from @tmp_data as xfilter 
where 
 xInvoice.invoice_no=xfilter.invoice_no 
and xInvoice.INVOICE_DATE=xfilter.invoice_date
) 
order by cast(invoice_date as 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 , Nomor dengan tanggal sudah ada dalam Data Invoice :' + @pemisah+  @pemisah+ @resultX + @pemisah+  @pemisah+ ' Periksa kembali data Invoice ' ;
  GOto f
end    


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

declare @tmp_invoice table (
 ms_manual_invoice_id bigint primary key
,invoice_no varchar(50) 
,invoice_date date 
,req_eta_date date 
,shipping_by varchar(50) 
,kode_supplier varchar(50) 
,rec_date date 
);
 
  
declare @ms_file_h_status_id bigint

BEGIN TRANSACTION;   
 
INSERT INTO dt_ms_manual_invoice
                         (ms_file_id
      , ms_manual_invoice_type_id 
      , invoice_no 
      , invoice_date 
      , req_eta_date 
      , shipping_by 
      , kode_supplier
      , rec_date 
      , mode_source_out
      , source_out_id 
      , create_date
      , create_by
       )
        
output inserted.ms_manual_invoice_id
      ,inserted.invoice_no
       , inserted.invoice_date
       , inserted.req_eta_date
       , inserted.shipping_by
       , inserted.kode_supplier
       , inserted.rec_date  
       into @tmp_invoice
select    @ms_file_id
,  @ms_manual_invoice_type_id
,  invoice_no
 ,cast(invoice_date as date)  
,cast(req_eta_date as date)
,shipping_by 
,kode_supplier 
,cast(rec_date as date)  
, - 1, - 1,@now,@data_by
from @tmp_data
group by  
   invoice_no
 ,cast(invoice_date as date)  
,cast(req_eta_date as date)
,shipping_by 
,kode_supplier 
,cast(rec_date as date) 

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.req_eta_date as date)= source.req_eta_date
 and cast(target.rec_date as date) = source.rec_date
 and target.shipping_by = source.shipping_by 
 and target.kode_supplier = source.kode_supplier
 )  
    WHEN MATCHED THEN   
        UPDATE SET target.ms_manual_invoice_id = source.ms_manual_invoice_id  ;  
  

INSERT INTO dt_ms_manual_invoice_detail
                         (ms_manual_invoice_id
, ms_tpb_in_barang_id
,kode_barang 
,part_no 
,order_qty 
,order_no 
,order_date 
,req_eta_date 
,inv_qty 
,unit 
,rec_qty
,istatus 
,price 
,amount 
,remark 
,bruto 
,netto 
, mapping_ms_item_id
, mapping_date
, mapping_by
, create_date
, create_by)
select         ms_manual_invoice_id
, -1
,kode_barang 
,part_no 
,order_qty 
,order_no 
 ,cast(order_date as date)  
 ,cast(req_eta_date as date)  
,inv_qty 
,unit 
,rec_qty
,istatus 
,price 
,amount 
,remark 
,bruto 
,netto 
, 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_invoice_manual_prosses CURSOR FOR  
select ms_manual_invoice_id ,  kode_supplier
from @tmp_invoice   
OPEN map_cursor_invoice_manual_prosses  
FETCH NEXT FROM map_cursor_invoice_manual_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_code))=@cur_SHIPPER
 
  if @new_ms_supplier_customer_id is null
  begin
   select top (1)
   @new_ms_supplier_customer_id=ms_supplier_customer_id  
   from dt_ms_supplier_customer 
   where supplier_customer_name=@cur_SHIPPER 
  end    
  update dt_ms_manual_invoice
  set mapping_supplier_customer_id =@new_ms_supplier_customer_id 
  , mapping_date=@NOW 
  , mapping_by =@data_by 
  where ms_manual_invoice_id =@cur_ID

    FETCH NEXT FROM map_cursor_invoice_manual_prosses INTO @cur_ID,@cur_SHIPPER
    END  

CLOSE map_cursor_invoice_manual_prosses  
DEALLOCATE map_cursor_invoice_manual_prosses


-- exec('insert into '+ @tbl_supplier_temp +' ( SUPP_CODE, SUPP_NAME, KD_NEGARA, BASE_PRICE, COPPERBASE_REF, SUPP_GCT, SUPPCODE_AS400 )
-- select SUPP_CODE, SUPP_NAME, KD_NEGARA, BASE_PRICE, COPPERBASE_REF, SUPP_GCT, SUPPCODE_AS400
   
-- from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
-- ''Data Source='+ @filedata +';
-- Extended properties=Excel 8.0;''
-- )...[''Sheet 1$'']
   
-- delete from '+ @tbl_supplier_temp +' where SUPP_CODE is null
-- select @c_file_url
--select * from dbo.axSheetList( @c_file_url )
--select * from dbo.axColumnListBySheetName( @c_file_url,'INVOICE$' )
 
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

--exec MG_MS_MANUAL_INVOICE_FILE_PROSSES
--'12MG_MS_MANUAL_INVOICE_FILE_PROSSES2::12021
--'
GO