set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go










-- =============================================
-- Author:		<Author,,Yonghui Xiao>
-- Create date: <Create Date, >
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_AIO] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	declare @cr_lot varchar(12)
	declare @cr_output_time datetime
	declare @cr_comp_time datetime
	declare @cr_output_qty int
	declare @cr_evt varchar(4)
	declare @sqlstr1 varchar(3000)
	declare @sqlstr2 varchar(3000)
	declare @sqlstr3 varchar(3000)

--print substring(convert(varchar(10),getdate(),20),3,2)+substring(convert(varchar(10),getdate(),20),6,2)
----------------------------------------------below is output search--------------------------------------------
update XXDB.dbo.AIO
set aio.output_time=null
from XXDB.dbo.AIO aio,XXDB.dbo.Awip_lot awip
where awip.XX1='XX' and awip.XX_time>convert(varchar(10),dateadd(day,-30,getdate()),20)
	and awip.XX_Id=aio.XX_id
/*find OX*******************XX_grade,XX_grade*************************************************/
--declare @cr_lot varchar(12)
declare @cr_sht varchar(12)
declare @cr_grade varchar(12)
declare @pv_lot varchar(12)
declare @find_lot int
declare @sht_grade varchar(60)
set @sht_grade=''
declare @pnl_grade varchar(1)
declare @pnl_cnt int
declare @grd_cnt int
declare @temp_cnt int
declare @cr_time datetime
declare @pv_time datetime
set @temp_cnt=0  
declare @sqlstr8 varchar(3000)
set @sqlstr8='
declare ox_cur cursor for 
select lot_id,sht_id,pnl_grade,crsp_time
from openquery(XXXXXX,''
	select XX_id,XX_id,substr(XX_grade,1,12) XX_grade,XX_timestamp XX_time
	from XXDB.XX
	where evt_cate=''''XXXX''''
	and XX_timestamp>timestamp('''''+ convert(varchar(10),dateadd(day,-30,getdate()),20)+' 1:00:00'+ ''''')
	and substr(lot_id,1,2) in (''''1X'''',''''1X'''')
	order by XX_id,XX_timestamp desc,XX_id
''
)
'
print @sqlstr8
exec(@sqlstr8)
open ox_cur
fetch next from ox_cur into @cr_lot,@cr_sht,@cr_grade,@cr_time
set @pv_time=@cr_time
while @@fetch_status=0
begin
	select @find_lot=count(*) 
		from XXDB.dbo.AIO
		where XX_id=@cr_lot
--		and XX_time is null
--			and XX_time >cast(convert(varchar(10),dateadd(day,-1,getdate()),20)+' 1:00:00' as datetime)
	if(@find_lot<>0)
--print cast(convert(varchar(10),getdate(),20)+' 1:00:00' as datetime)
	begin
		if(@pv_lot<>@cr_lot)
		begin
			update XXDB.dbo.AIO
				set XX_grade=@sht_grade,XX_cnt=@temp_cnt
				where XX_id=@pv_lot
			set @sht_grade=''
			set @temp_cnt=0
			set @pv_time=@cr_time
		end
		if(@pv_time=@cr_time)
		begin
			set @pnl_cnt=0
			set @grd_cnt=0
			while @pnl_cnt<13
			begin
				if(substring(@cr_grade,@pnl_cnt+1,1))='O' set @grd_cnt=@grd_cnt+1
				set @pnl_cnt=@pnl_cnt+1
			end
			if @grd_cnt=10 set @pnl_grade='A'
			else if @grd_cnt=11 set @pnl_grade='B'
			else if @grd_cnt=12 set @pnl_grade='C'
			else set @pnl_grade=cast(@grd_cnt as varchar(1))
			set @sht_grade=@sht_grade+@pnl_grade
			set @temp_cnt=@temp_cnt+@grd_cnt
		end
	end
	
	set @pv_lot=@cr_lot
	fetch next from ox_cur into @cr_lot,@cr_sht,@cr_grade,@cr_time
end

deallocate ox_cur
/***************** output_time,output_qty,XXXX modify******************/
set @sqlstr1='
	declare output_XX_cur cursor for
	select XX_id 
	from XXDB.dbo.AIO
	where XX_time is null
	for update
	'
--print @sqlstr
exec(@sqlstr1)
open output_lot_cur
fetch next from output_XX_cur into @cr_lot
--print @@fetch_status
while @@fetch_status=0
begin
	set @sqlstr2='
	declare cr_cur cursor for
	select XX_time,XX_cate,XX
	from XXDB.dbo.Awip_lot
	where XX_id='''+@cr_lot+''' and XX_cate in(''XXXX'',''XXXX'')
	order by evt_time
	'
	exec(@sqlstr2)
	open cr_cur
	fetch next from cr_cur into @cr_output_time,@cr_evt,@cr_output_qty
	while @@fetch_status=0
	begin
		if @cr_evt='CRSP'
		begin
			update XXDB.dbo.AIO
			set XX_time=@cr_output_time,XX_qty=@cr_output_qty
			where current of output_XX_cur	
			/**************scrp***************************/
			update XXDB.dbo.AIO
			set aio.XX=sc.XX
			from (
			select XX_id,sum(s)-sum(c) XXXX
			from (
			select XX_id,
			case when XX_cate in ('XXXX','XXXX','XXXX','XXXX') then XXX else 0  end s,
			case when XX_cate in ('XXXX','XXXX','XXXX') then XXX else 0 end c 
			from XXDB.dbo.XXXX_XXX
			where evt_cate in('XXXX','XXXX','XXXX','XXXX','XXXX','XXXX','XXXX')
				and XXX_id =@cr_lot
			)temp
			group by XX_id
			) sc,XXDB.dbo.AIO aio
			where sc.XX_id=aio.XX_id
		end
		else if @cr_evt='XXXX'
			update XXDB.dbo.AIO
			set complete_time=@cr_output_time
			where current of output_XX_cur	
		fetch next from cr_cur into @cr_output_time,@cr_evt,@cr_output_qty
	end
	deallocate cr_cur
	fetch next from output_lot_cur into @cr_lot
end
deallocate output_lot_cur

-------XXXX quantity
--declare @cr_lot varchar(12)
declare @sc int
declare @input_no int
declare @sc_time datetime
declare sc_cur cursor for
	select tempsc.XXX_id XXX_id,sum(tempsc.XXXX) XXXX,max(sc_time)
	from(
		select lot_id,
			case when evt_cate in('XXXX' , 'XXXX' , 'XXXX', 'XXXX') then qty end XXXX,
			case when evt_cate in('XXXX' , 'XXXX' , 'XXXX') then XX_time end XX_time
		from XXDB.dbo.Awip_lot
		where lot_id in(
			select lot_id from XXDB.dbo.AIO
				where output_time is null
		)
		)tempsc
	where XXXX is not null
	group by XX_id
	order by XXXX desc
open sc_cur
fetch next from sc_cur into @cr_lot,@sc,@sc_time
while @@fetch_status=0
begin
	select @input_no=input_qty
		from XXDB.dbo.AIO
	where lot_id=@cr_lot
	if @input_no=@sc
	update XXDB.dbo.AIO
		set XXXXXX_time=@sc_time,XXXXXX_qty=0,
			XX_cnt=0,XXX_grade='XXXXXXXXXXXXXXXXXXXXXXX'
		where XXX_id=@cr_lot
	fetch next from sc_cur into @cr_lot,@sc,@sc_time
end
deallocate sc_cur
----------------------------------------------below is input search------------------------------
set @sqlstr3='
	insert into XXDB.dbo.AIO(XX_id,XXXX,XXXXXX_time,XXXXX_qty)
	select XXX_id,XXXX,XXX_time,XXX
	from XXDB.dbo.Awip_lot
	 where evt_cate=''XXXX'' and XXX_id not in
		(select XXX_id 
		from XXDB.dbo.AIO
		)
	
	'
exec(@sqlstr3)

declare @sqlstr4 varchar(6000)
declare @prty_null varchar(6000)
set @prty_null=''
--declare @p_null int
declare @null_lot varchar(10)
declare null_cur cursor 
	for
	select lot_id from XXDB.dbo.AIO where XXXXXX is null
open null_cur
fetch next from null_cur into @null_lot
while @@fetch_status=0
begin
	set @prty_null=@prty_null+''''''+@null_lot+''''''+','
	--set @p_null=@p_null+1
	--print(@null_lot)
	fetch next from null_cur into @null_lot
end
set @prty_null=@prty_null+''''''''''
--print(@prty_null)
--print(@p_null)
deallocate null_cur
/**************    priority*    *************/
set @sqlstr4='
update XXDB.dbo.AIO
set priority=XXDB.XXXX from 
	(select XXX_id,XXXX from
	openquery(XXXXXX,
	''select XXX_id, max(XXXX) XXXX 
		from XXXXXX.XXXXXX
		where XXX_id in ('+
			@prty_null+')
		group by XXX_id
	''))
	db2,XXDB.dbo.AIO aio
where aio.XXX_id=XXX.XXX_id
'

print(@sqlstr4)
exec(@sqlstr4)


END










