set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UP_PP_SemCheckReport]
@MaterialID VARCHAR(MAX)
,@ShiftName VARCHAR(MAX)
,@MachineID VARCHAR(MAX)
,@StartTime VARCHAR(MAX)
,@EndTime VARCHAR(MAX)
AS
SET NOCOUNT ON;
DECLARE @strMaterialID VARCHAR(MAX);
DECLARE @strShiftName VARCHAR(MAX);
DECLARE @strMachineID VARCHAR(MAX);
DECLARE @strStartTime VARCHAR(MAX);
DECLARE @strEndTime VARCHAR(MAX);
SET @strStartTime = ''''''+@StartTime+'''''';
SET @strStartTime = SUBSTRING(@strStartTime,2,LEN(@strStartTime)-2);
SET @strEndTime = ''''''+@EndTime+'''''';
SET @strEndTime = SUBSTRING(@strEndTime,2,LEN(@strEndTime)-2);
IF CHARINDEX(@MaterialID,',') >= 0
BEGIN
SET @strMaterialID = REPLACE(@MaterialID,',',''',''');
SET @strMaterialID = ''''''+@strMaterialID+'''''';
SET @strMaterialID = SUBSTRING(@strMaterialID,2,LEN(@strMaterialID)-2);
END;
ELSE
BEGIN
SET @strMaterialID = @MaterialID;
END;
IF CHARINDEX(@ShiftName,',') >= 0
BEGIN
SET @strShiftName = REPLACE(@ShiftName,',',''',''');
SET @strShiftName = ''''''+@strShiftName+'''''';
SET @strShiftName = SUBSTRING(@strShiftName,2,LEN(@strShiftName)-2);
END;
ELSE
BEGIN
SET @strShiftName = @MaterialID;
END;
IF CHARINDEX(@MachineID,',') >= 0
BEGIN
SET @strMachineID = REPLACE(@MachineID,',',''',''');
SET @strMachineID = ''''''+@strMachineID+'''''';
SET @strMachineID = SUBSTRING(@strMachineID,2,LEN(@strMachineID)-2);
END;
ELSE
BEGIN
SET @strMachineID = @MachineID;
END;
EXEC('
SELECT
DISTINCT
O.CardNo
, CONVERT(DATETIME,('+'''20'''+'+LEFT(RIGHT(O.CardNo,10),6))) AS ProductTime
, LEFT(O.CardNo,7) AS MachineID
, O.MaterialID
, M.MaterialName
, O.ShiftName
, O.Seq
, O.Weight
, CONVERT(FLOAT,LEFT(C.totalcount,4)) AS C
, (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00) AS T
, (CONVERT(FLOAT,LEFT(C.totalcount,4)) + CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))) AS USL
, (CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))) AS LSL
, (SR.AVG_Weight) AS X
, (SR.STDEV_Weight) AS OMG
, ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00) AS CA
,
CASE
WHEN CONVERT(FLOAT,ABS(((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) <= 0.125
THEN '+'''A'''+'
WHEN CONVERT(FLOAT,ABS(((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) <= 0.250
AND CONVERT(FLOAT,ABS(((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) > 0.125
THEN '+'''B'''+'
WHEN CONVERT(FLOAT,ABS(((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) > 0.250
AND CONVERT(FLOAT,ABS(((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) <= 0.500
THEN '+'''C'''+'
WHEN CONVERT(FLOAT,ABS(((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00)))> 0.500
THEN '+'''D'''+'
END CA_LEVEL
, (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) AS CP
,
CASE
WHEN (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) >= 1.67
THEN '+'''A+'''+'
WHEN (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) < 1.67
AND (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) >= 1.33
THEN '+'''A'''+'
WHEN (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) >= 1.00
AND (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) < 1.33
THEN '+'''B'''+'
WHEN (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) >= 0.67
AND (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) < 1.00
THEN '+'''C'''+'
WHEN (CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) <0.67
THEN '+'''D'''+'
END CP_LEVEL
, CONVERT(DECIMAL,((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00)))) AS CPK
, CASE
WHEN ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) >= 1.67
THEN '+'''A+'''+'
WHEN ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) < 1.67
AND ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) >= 1.33
THEN '+'''A'''+'
WHEN ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) >= 1.00
AND ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) < 1.33
THEN '+'''B'''+'
WHEN ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) >= 0.67
AND ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) < 1.00
THEN '+'''C'''+'
WHEN ((CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4)))* 2.00)/(CONVERT(FLOAT,SR.STDEV_Weight)*6.00)) * (1.00- ((CONVERT(FLOAT,LEFT(C.totalcount,4)) - CONVERT(FLOAT,SR.AVG_Weight))/(CONVERT(FLOAT,RIGHT(C.totalcount,LEN(LEFT(C.totalcount,4))))* 2.00)*2.00))) <0.67
THEN '+'''D'''+'
END CPK_LEVEL
FROM
dbo.tb_PP_OutputDetailReal AS O
LEFT JOIN dbo.tb_te_construction AS C
ON O.MaterialID = C.standard
LEFT JOIN dbo.tb_TE_Material AS M
ON O.MaterialID = M.MaterialID
LEFT JOIN dbo.tb_QU_SemiCheck AS S
ON O.MaterialID = S.MaterialID
AND O.ShiftName = S.ShiftID
AND O.ArtEquipID = S.EquipID
LEFT JOIN tb_PP_OutputDetailRealSum AS SR
ON O.MaterialID = SR.MaterialID
WHERE C.checkflg = '+'''1'''+'
AND O.MaterialID IN ('+@strMaterialID+')
AND LEFT(O.CardNo,7) IN ('+@strMachineID+')
AND O.ShiftName IN ('+@strShiftName+')
AND CONVERT(DATETIME,('+'''20'''+'+LEFT(RIGHT(O.CardNo,10),6))) >= '+@strStartTime+'
AND CONVERT(DATETIME,('+'''20'''+'+LEFT(RIGHT(O.CardNo,10),6))) <= '+@strEndTime+'
ORDER BY O.CardNo');