dbo.MG_UPLOAD_PART_LIST_DETAIL

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE procedure dbo.MG_UPLOAD_PART_LIST_DETAIL
@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)


--if (select count(id) from dt_fileupload where (orig_name = @orig_name) AND file_type_id = @file_type_id)>0
--begin
-- set @xStsno='02';
-- set @xStsdes='File sudah pernah di upload';
-- goto f
--end

--INSERT @listSheet (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, DATE_MODIFIED)
--EXEC dbo.cl_listSheet @full_path

--INSERT @listSheet (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_GUID, DESCRIPTION, TABLE_PROPID, DATE_CREATED, DATE_MODIFIED)
--EXEC dbo.cl_listSheet @full_path
select TOP 1 @sheet = TABLE_NAME from @listSheet

--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 top 1 replace(replace(sheet_name,'''',''),'$','') from dbo.axSheetList(@full_path) 
group by replace(replace(sheet_name,'''',''),'$','') 

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

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_date date,
@check_ms_part_list_change_part_status_id int,
@check_ms_part_list_change_part_status_name  varchar(255),
@check_ms_item_id bigint,
@check_ms_part_no 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_date = ms_part_list_change_part_date
 ,@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
 ,@check_ms_item_id = ms_item_id
 ,@check_ms_part_no = part_no
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

--select * from @listSheet

declare @excel_data table(
   rowindex BIGINT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
 , assy_code varchar(255)
 , F1 varchar(255)
 , F2 varchar(255)
 , F3 varchar(255)
 , F4 varchar(255)
 , F5 varchar(255)
 , F6 varchar(255)
 , F7 varchar(255)
 , F8 varchar(255)
 , F9 varchar(255)
 , F10 varchar(255)
 , F11 varchar(255)
 , F12 varchar(255)
 , F13 varchar(255)
 , F14 varchar(255)
)

--declare @part_list table(
-- assy_code varchar(100)
-- , assy_name varchar(100)
-- , assy_comb varchar(100)
-- , carline varchar(100)
-- , size varchar(100)
--)

declare @part_list_detail table(
   assy_code varchar(100)
 , part_no varchar(50)
 , part_name varchar(255)
 , a_f_no varchar(50)
 , molts_no varchar(50)
 , supplier varchar(50)
 , qty_jb decimal(18,4)
 , qty_total decimal(18,4)
 , rownumber varchar(50) 
)

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 (assy_code, F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14)
 exec(
' declare @skiprows int set @skiprows = 1 --SKIP THE FIRST ROWS select ''' + @TABLE_NAME +
''', * from openrowset (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=no;IMEX=1;Database=' + @full_path +
''' ,''select * from ['+  @TABLE_NAME +
'$]'') as a ')

 select @rowindex_start = rowindex from @excel_data where F1 = '1'

 insert into @part_list_detail(assy_code, part_no, part_name, a_f_no, molts_no, supplier, qty_jb, qty_total, rownumber)
 select @TABLE_NAME, F2, F4, F3, F5, F6, F7, F8, F1 from @excel_data where rowindex >= @rowindex_start

 delete from @part_list_detail where rownumber is null or part_no is null or part_name is null

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

DECLARE @check_part_no varchar(max)
SELECT @check_part_no = COALESCE(@check_part_no + ', ' + a.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

 -- insert ke part list detail
 INSERT INTO dt_ms_part_list_detail(ms_item_id, ms_item_part_id, a_f_no, molts_no, supplier, qty_jb, qty_total,qty)
 SELECT @check_ms_item_id, b.ms_item_id, a_f_no, molts_no, supplier, qty_jb, qty_total, 0 from @part_list_detail a 
 INNER JOIN dt_ms_item b ON a.part_no = b.part_no
 WHERE b.ms_item_id is not null

 -- insert ke change part list detail
 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_jb, 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
 WHERE a.ms_item_id is not null

 -- Supply data change part list

 DECLARE @cur_ms_part_list_production_detail_id bigint
 , @cur_ms_item_id bigint
 , @cur_quantity decimal(18,4)
 , @cur_total_supply decimal(18,4)

 DECLARE production_cursor CURSOR FOR 
 
  SELECT ms_part_list_change_part_detail_id, ms_item_id, quantity, total_supply
  FROM vw_part_list_change_part_detail
  WHERE (ms_part_list_change_part_id = @check_ms_part_list_change_part_id) 
  AND  total_supply<quantity

    OPEN production_cursor  
    FETCH NEXT FROM production_cursor INTO @cur_ms_part_list_production_detail_id  
             , @cur_ms_item_id  
             , @cur_quantity 
             , @cur_total_supply 
    WHILE @@FETCH_STATUS = 0  
    BEGIN  

 declare @ms_production_in_detail_id bigint
 , @doc_date date
 , @quantity decimal(18,4)
 , @is_sumber int
 SELECT @ms_production_in_detail_id = ms_production_in_detail_id
 , @doc_date = doc_date
 , @quantity = quantity
 , @is_sumber = is_sumber
 FROM  dbo.fn_ms_auto_supply_production_part_list(@cur_ms_item_id, 0, @check_ms_part_list_change_part_date, @cur_quantity - @cur_total_supply)
  
 if @is_sumber = 0
  begin
   INSERT INTO dt_ms_part_list_out (ms_item_out_detail_id, ms_trans_type_id, ms_part_list_process_detail_id , ms_process_id, quantity, quantity_new_part, quantity_recycle)
   VALUES (@ms_production_in_detail_id,1,@cur_ms_part_list_production_detail_id,2,@quantity,@quantity,0)
  end
 else
  begin
   INSERT INTO dt_ms_part_list_out (ms_item_out_detail_id, ms_trans_type_id, ms_part_list_process_detail_id , ms_process_id, quantity, quantity_new_part, quantity_recycle)
   VALUES (@ms_production_in_detail_id,2,@cur_ms_part_list_production_detail_id,2,@quantity,@quantity,0)
  end
 
 FETCH NEXT FROM production_cursor INTO @cur_ms_part_list_production_detail_id  
          , @cur_ms_item_id  
          , @cur_quantity 
          , @cur_total_supply 
    END  

    CLOSE production_cursor  
    DEALLOCATE production_cursor 

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