dbo.MG_UPLOAD_PART_LIST_OLD

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE procedure dbo.MG_UPLOAD_PART_LIST_OLD
@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

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

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)

--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 @assy_code varchar(100)
 , @assy_name varchar(100)
 , @assy_comb varchar(100)
 , @carline varchar(100)
 , @size varchar(100)
 , @rowindex_start bigint
 , @ms_item_id bigint
 , @ms_part_list_id 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 @assy_name = ltrim(replace(F4,':','')) from @excel_data where F1 = 'PROGRAM'
 select @assy_comb = ltrim(replace(F4,':','')) from @excel_data where F1 = 'ASSY COMB.'
 select @carline = ltrim(replace(F4,':','')) from @excel_data where F1 = 'CARLINE'
 select @size = ltrim(replace(F9,':','')) from @excel_data where F8 = 'SIZE J/B'
 select @rowindex_start = rowindex from @excel_data where F1 = '1'

 insert into @part_list (assy_code, assy_name, assy_comb, carline, size) VALUES (@TABLE_NAME, @assy_name, @assy_comb, @carline, @size)

 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

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

declare @check_part_no varchar(max), @check_assy_code varchar(max)

select @check_assy_code = COALESCE(@check_assy_code + ', ' + a.assy_code, a.assy_code) 
from @part_list a where exists(
 select part_no from dt_ms_item where part_no = a.assy_code
)

if @check_assy_code is not null
 begin
  set @xStsno='02';
  set @xStsdes= 'Assy Code sudah ada di master :
' + @check_assy_code;
  goto f
 end

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
)

select @check_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

declare @ms_type_id int = 11
, @ms_item_custom_no varchar(50) = 'IE018'
, @base_no varchar(50) = 'JIG BOARD'
, @ms_item_h_status_id bigint
, @ms_item_list_price_id bigint

declare @date_end_sta date, @ms_date_business_id_sta bigint
SELECT     @date_end_sta=   end_date
,@ms_date_business_id_sta=  dt_ms_date_business.ms_date_business_id
FROM            dt_ms_cost_final_avg INNER JOIN
                         dt_ms_date_business ON dt_ms_cost_final_avg.end_date = dt_ms_date_business.ms_date_business
WHERE        (dt_ms_cost_final_avg.ms_cost_final_avg_id = 0)

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()

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

 Declare loopchecking CURSOR FOR SELECT assy_code, assy_name, assy_comb, carline, size  from @part_list
 OPEN loopchecking
 FETCH NEXT FROM loopchecking INTO @assy_code, @assy_name, @assy_comb, @carline, @size
 WHILE @@FETCH_STATUS = 0 
 BEGIN
 
  INSERT INTO dt_ms_item
        (ms_type_id, ms_item_custom_no, ms_item_base_no, part_no, part_name, uom, consumable, create_date, create_by, modify_date, modify_by, qty_minimum)
  VALUES        (@ms_type_id,@ms_item_custom_no,@base_no,@assy_code,@assy_name,'PCE',0,@now,@data_by,@now,@data_by,0)
  set @ms_item_id=SCOPE_IDENTITY();

  INSERT INTO dt_ms_item_h_status
         (ms_item_id, ms_item_status_id, status_info, create_date, create_by)
  VALUES        (@ms_item_id, 1, N'',@now,@data_by)
  set @ms_item_h_status_id=SCOPE_IDENTITY();
 
   --- MASUKAN STA AWAL----
  INSERT INTO dt_ms_item_list_price
  (ms_cost_final_avg_id, ms_item_id, end_qty, end_price_usd , end_price, create_date, create_by, modify_date, modify_by)

  VALUES        (0,@ms_item_id,0,0, 0,@now,@data_by,@now,@data_by)
  set @ms_item_list_price_id= SCOPE_IDENTITY();
 
  INSERT INTO dt_ms_item_daily_in_move_avg
         (ms_date_business_id, ms_item_id, total_qty_avg_move, price_avg_move_idr, price_avg_move_usd, ms_item_h_price_id, ms_cost_final_avg_id)
  VALUES        (@ms_date_business_id_sta,@ms_item_id,0,0,0,@ms_item_list_price_id,0)

  INSERT INTO dt_ms_mutasi_item
         (ms_cost_final_avg_id, ms_item_id, qty_end, qty_adjutment)
  values(0, @ms_item_id, 0, 0)
 
  update dt_ms_item
  set ms_item_h_status_id=@ms_item_h_status_id
  where ms_item_id=@ms_item_id

  insert into dt_ms_part_list(ms_item_id,assy_comb,carline,size,create_date,create_by,modify_date,modify_by)
  values(@ms_item_id,@assy_comb,@carline,@size,@now,@data_by,@now,@data_by)

  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 @ms_item_id, b.ms_item_id, a_f_no, molts_no, supplier, qty_jb, qty_total, 0 from @part_list_detail a 
  left join dt_ms_item b ON a.part_no = b.part_no
  where a.assy_code = @assy_code

  FETCH NEXT FROM loopchecking INTO  @assy_code, @assy_name, @assy_comb, @carline, @size
 END
 CLOSE loopchecking    
 DEALLOCATE loopchecking

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