dbo.MG_UPLOAD_CHANGE_PART_LIST

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE procedure dbo.MG_UPLOAD_CHANGE_PART_LIST
@PARAMETER xml='' 
---XencX---with encryption
as

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

declare @resultX nvarchar(max) 
declare @resultX2 nvarchar(max)

declare @XmlSts xml
declare @raw xml
declare @xStsnoheader nvarchar(2),
 @xStsno nvarchar(2),
 @xStsdes nvarchar(max)
   
set @xStsnoheader='07'
set @xStsno='00';
set @xStsdes='sukses'

  
SET NOCOUNT ON
exec  MG_CHECK_CREATE_PERIODE @xStsno output,@xStsdes output;
if not(
@xStsno ='00'
)
begin
   set @XmlSts=(select @xStsnoheader + @xStsno as 'no', @xStsdes as 'des' for xml path('sts'))
  goto g 
end
SET NOCOUNT OFF

BEGIN TRY
SET NOCOUNT ON
DECLARE @NOW datetime
,@rowJSON varchar(max);

set @NOW =GETDATE() 
 
 
declare @data_by nvarchar(20)
, @error int = 0
, @uploadid bigint
, @formatfile VARCHAR(255)
, @rowsheet bigint = 1
, @err_msg varchar(max) = ''
, @err int = 0
, @sp_str varchar(max) = ''
, @str nvarchar(1000) = ''

declare @sheet nvarchar(255)

DECLARE  @listSheet TABLE (
    TABLE_CATALOG nvarchar(255),
    TABLE_SCHEMA nvarchar(255),
    TABLE_NAME nvarchar(255),
 TABLE_TYPE nvarchar(255),
 TABLE_GUID nvarchar(255),
 DESCRIPTION nvarchar(255),
 TABLE_PROPID nvarchar(255),
 DATE_CREATED datetime,
 DATE_MODIFIED datetime
)
    
declare @file_name nvarchar(255)  
,@file_type nvarchar(255) 
,@file_path nvarchar(500) 
,@full_path nvarchar(1000)
,@raw_name nvarchar(255) 
,@orig_name nvarchar(255) 
,@client_name nvarchar(255) 
,@file_ext nvarchar(255) 
,@file_size nvarchar(255) 
,@is_image nvarchar(255) 
,@image_width nvarchar(255) 
,@image_height nvarchar(255) 
,@image_type nvarchar(255) 
,@image_size_str nvarchar(255) 
,@file_type_id int

,@assets_list_id bigint
,@part_no varchar(255)

SELECT
     @data_by=tempTable.item.value('data_by[1]', 'nvarchar(255)') 
 ,@file_name=left(ltrim(rtrim(isnull(tempTable.item.value('file_name[1]', 'nvarchar(285)'),''))),255) 
 ,@file_type=left(ltrim(rtrim(isnull(tempTable.item.value('file_type[1]', 'nvarchar(285)'),''))),255) 
 ,@file_path=left(ltrim(rtrim(isnull(tempTable.item.value('file_path[1]', 'nvarchar(530)'),''))),500) 
 ,@full_path=left(ltrim(rtrim(isnull(tempTable.item.value('full_path[1]', 'nvarchar(1030)'),''))),1000) 
 ,@raw_name=left(ltrim(rtrim(isnull(tempTable.item.value('raw_name[1]', 'nvarchar(285)'),''))),255) 
 ,@orig_name=left(ltrim(rtrim(isnull(tempTable.item.value('orig_name[1]', 'nvarchar(285)'),''))),255) 
 ,@client_name=left(ltrim(rtrim(isnull(tempTable.item.value('client_name[1]', 'nvarchar(285)'),''))),255) 
 ,@file_ext=left(ltrim(rtrim(isnull(tempTable.item.value('file_ext[1]', 'nvarchar(285)'),''))),255) 
 ,@file_size=left(ltrim(rtrim(isnull(tempTable.item.value('file_size[1]', 'nvarchar(285)'),''))),255) 
 ,@is_image=left(ltrim(rtrim(isnull(tempTable.item.value('is_image[1]', 'nvarchar(285)'),''))),255) 
 ,@image_width=left(ltrim(rtrim(isnull(tempTable.item.value('image_width[1]', 'nvarchar(285)'),''))),255) 
 ,@image_height=left(ltrim(rtrim(isnull(tempTable.item.value('image_height[1]', 'nvarchar(285)'),''))),255) 
 ,@image_type=left(ltrim(rtrim(isnull(tempTable.item.value('image_type[1]', 'nvarchar(285)'),''))),255) 
 ,@image_size_str=left(ltrim(rtrim(isnull(tempTable.item.value('image_size_str[1]', 'nvarchar(285)'),''))),255) 
 ,@file_type_id=left(ltrim(rtrim(isnull(tempTable.item.value('file_type_id[1]', 'int'),''))),255) 

 ,@assets_list_id=isnull(tempTable.item.value('assets_list_id[1]', 'bigint'),-1)
 ,@part_no=left(ltrim(rtrim(isnull(tempTable.item.value('part_no[1]', 'varchar(285)'),''))),255)
FROM @PARAMETER.nodes('param/field')  tempTable(item)



select TOP 1 @sheet = sheet_name from dbo.axSheetList(@full_path)

delete from @listSheet where TABLE_NAME like '%FilterDatabase%'
delete from @listSheet where TABLE_NAME like '%Print_Area%'

insert into @listSheet (TABLE_NAME)
select replace(replace(sheet_name,'''',''),'$','') from dbo.axSheetList(@full_path) 
group by replace(replace(sheet_name,'''',''),'$','')

--update @listSheet set TABLE_NAME = replace(replace(TABLE_NAME,'''',''),'$','')

declare @TABLE_NAME  nvarchar(255)

if @part_no =''
 begin
  set @xStsno='02';
  set @xStsdes='Gagal, Nomor Change Part tidak boleh kosong' ;
  goto f
 end

-- cek header change part
declare @check_ms_part_list_change_part_id bigint,
@check_ms_part_list_change_part_no varchar(255),
@check_ms_part_list_change_part_status_id int,
@check_ms_part_list_change_part_status_name  varchar(255)

select 
  @check_ms_part_list_change_part_id = ms_part_list_change_part_id
 ,@check_ms_part_list_change_part_no = ms_part_list_change_part_no
 ,@check_ms_part_list_change_part_status_id = ms_part_list_change_part_status_id
 ,@check_ms_part_list_change_part_status_name= ms_part_list_change_part_status_name
from vw_part_list_change_part_header 
where ms_part_list_change_part_no = @part_no

if @check_ms_part_list_change_part_no is null
 begin
  set @xStsno='02';
  set @xStsdes='Gagal, Save Header terlebih dahulu' ;
 goto f
 end 

if not(@check_ms_part_list_change_part_status_id=0)
 begin
  set @xStsno='02';
  set @xStsdes='Gagal, Dokumen dengan status ' + @check_ms_part_list_change_part_status_name + ' tidak bisa di rubah ' ;
 goto f 
 end

declare @excel_data table(
   rowindex BIGINT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
 , F1 varchar(255)
 , F2 varchar(255)
 , F3 varchar(255)
)

declare @part_list_detail table(
   part_no varchar(50)
 , part_name varchar(255)
 , qty decimal(18,4)
)

declare @rowindex_start bigint

IF CURSOR_STATUS('global','loopchecking')>=-1
BEGIN
 DEALLOCATE loopchecking
END

Declare loopchecking CURSOR FOR SELECT TABLE_NAME from @listSheet
OPEN loopchecking
FETCH NEXT FROM loopchecking INTO @TABLE_NAME
WHILE @@FETCH_STATUS = 0 
BEGIN
 
 --delete from @excel_data
 --insert into @excel_data (F1,F2,F3)
 exec(
' declare @skiprows int set @skiprows = 1 --SKIP THE FIRST ROWS select * from openrowset (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=no;IMEX=1;Database=' + @full_path +
''' ,''select * from ['+  @TABLE_NAME +
'$]'') as a ')

 --select * from @excel_data

 --select @rowindex_start = rowindex from @excel_data
 select @rowindex_start = rowindex from @excel_data where rowindex = '2'
 --select @rowindex_start

 insert into @part_list_detail(part_no, part_name, qty)
 select F1, F2, F3 from @excel_data where rowindex >= @rowindex_start

 --select * from @part_list_detail

 FETCH NEXT FROM loopchecking INTO @TABLE_NAME
END
CLOSE loopchecking    
DEALLOCATE loopchecking

declare @check_part_no varchar(max)

select @check_part_no = a.part_no
from @part_list_detail a where not exists(
 select part_no from dt_ms_item where part_no = a.part_no
)

if @check_part_no is not null
 begin
  set @xStsno='02';
  set @xStsdes= 'Part No belum ada di master :
' + @check_part_no;
  goto f
 end

BEGIN TRANSACTION;
 INSERT INTO dt_fileupload (file_name, file_type, file_path, full_path, raw_name, orig_name, client_name, file_ext, file_size, is_image, image_width, image_height, image_type, image_size_str, file_type_id, createby)
 VALUES (@file_name, @file_type, @file_path, @full_path, @raw_name, @orig_name, @client_name, @file_ext, @file_size, @is_image, @image_width, @image_height, @image_type, @image_size_str,@file_type_id, @data_by)
 
 SET @uploadid = SCOPE_IDENTITY()

 DELETE FROM dt_ms_part_list_change_part_detail WHERE ms_part_list_change_part_id = @check_ms_part_list_change_part_id

 DECLARE @ms_item_id bigint

 INSERT INTO dt_ms_part_list_change_part_detail(ms_part_list_change_part_id, ms_item_id, quantity, quantity_new_part, quantity_recycle, quantity_supply, create_date, create_by, modify_date, modify_by)
 SELECT @check_ms_part_list_change_part_id, ms_item_id, qty, 0, 0, 0, @NOW, @data_by, @NOW, @data_by 
 FROM dt_ms_item a
 INNER JOIN @part_list_detail b on a.part_no = b.part_no
 
ROLLBACK;    
  
   
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 ,'')  ,isnull( @rowJSON, '') as jsondata for xml path ('result')
)  as XML)  as dt


GO