USE [ChangHong_612]

GO

/****** Object: StoredProcedure [dbo].[st_MES_UpdateInspectResult] Script Date: 10/09/2015 16:15:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: luoxf

-- Create date: 20141023

-- Description: 审核检测

-- =============================================

ALTER PROCEDURE [dbo].[st_MES_UpdateInspectResult] (

@BillNo nvarchar(50),

@CheckMan nvarchar(20),

@Result nvarchar(10)

)

AS

BEGIN

--select abnormalno,* from mes_Inspect where AbnormalNO is not null and checkman is not null order by CheckTime desc

if exists(select * from mes_Inspect where billno=@billno and checkman is not null)

return

if (@Result='NG')

begin

declare @InitValue_IP varchar(50),@Month varchar(10),@InitValue_F varchar(50),@InitValue_O varchar(50)

set @Month=month(getdate())

if (len(@Month)=1)

set @Month ='0'+@Month

select @InitValue_IP = isnull(max(substring(abnormalno,len(abnormalno)-2,len(abnormalno))),0)from MES_Inspect

where abnormalno<> '' and substring(abnormalno,1,4)=convert(varchar(4),getdate(),112) and abnormalno like '%IPQC-ZC-%'

select @InitValue_F = isnull(max(substring(abnormalno,len(abnormalno)-2,len(abnormalno))),0)from MES_Inspect

where abnormalno<> '' and substring(abnormalno,1,4)=convert(varchar(4),getdate(),112) and abnormalno like '%FQC-ZC-%'

select @InitValue_O = isnull(max(substring(abnormalno,len(abnormalno)-2,len(abnormalno))),0)from MES_Inspect

where abnormalno<> '' and substring(abnormalno,1,4)=convert(varchar(4),getdate(),112) and abnormalno like '%OQC-ZC-%'

select @InitValue_IP = convert(int,@InitValue_IP)+1

select @InitValue_F = convert(int,@InitValue_F)+1

select @InitValue_O = convert(int,@InitValue_O)+1

update mes_Inspect

set abnormalno=convert(varchar(4),getdate(),112)+'MS-SL2-'+

case

when checktype in(1,2,3,4) then 'IPQC'+'-ZC-'+@Month+'-'+replace(str(right(@InitValue_IP,3), 3), space(1), '0')

when checktype=5 then

case when CONVERT(nvarchar,DateName(DAY,GETDATE()))='1'

then

case

when not exists(select * from mes_Inspect where abnormalno like '%'+'FQC'+'-ZC-'+@Month+'-'+'%')

then 'FQC'+'-ZC-'+@Month+'-'+'001'

else 'FQC'+'-ZC-'+@Month+'-'+replace(str(right(@InitValue_F,3), 3), space(1), '0')

end

else

'FQC'+'-ZC-'+@Month+'-'+replace(str(right(@InitValue_F,3), 3), space(1), '0')

end

when checktype=6 then 'OQC'+'-ZC-'+@Month+'-'+replace(str(right(@InitValue_O,3), 3), space(1), '0')

end

,

checkResult=@result,

checkman=@checkMan,

checktime=getdate()

where billno=@billno

end

else

begin

update mes_Inspect set checkResult=@result,checkman=@checkMan,checktime=getdate() where billno=@billno and checkman is null

update mes_inspectsingle set Itemvalue=case when itemvalue='NG' then 'OK' else Itemvalue end where billno=@BillNo

update MES_InspectGauge set CheckResult=case when CheckResult='NG' then 'OK' else CheckResult end where billno=@BillNo

end

end

--select abnormalno , * from mes_Inspect where abnormalno like '%'+'IPQC'+'-ZC-'+'07'+'-'+'%'