XinYu_RZMES/.svn/pristine/e4/e43c084be51e8f9f3ce7cd7f6f5022f634042beb.svn-base

<?xml version="1.0" encoding='UTF-8'?>
<queryMap desc="">
	
	<query  id="UIB020290_01.SELECT" desc="外观检验实绩">
		<![CDATA[
WITH RESULT AS (
    SELECT
         (SELECT COUNT(*) FROM TBH02_COIL_COMM
         WHERE EXTSHAPE_DEC_DTIME >= ?||'000000'
         AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
         AND   EXTSHAPE_DEC_GRD >= '1') CNT1_1                                  --合格钢卷数(日计)
         ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
         WHERE EXTSHAPE_DEC_DTIME >= ?||'000000'
         AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
         AND   EXTSHAPE_DEC_GRD = '1') CNT1_2                                   --合格钢卷数(日计)
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
		 WHERE EXTSHAPE_DEC_DTIME >= ?||'000000'
		 AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
		 AND   EXTSHAPE_DEC_GRD >= '2') CNT1_9                                  --不合格钢卷数(日计)	  
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
		 WHERE EXTSHAPE_DEC_DTIME >= ?||'000000'
         AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
         AND   EXTSHAPE_DEC_GRD = '2') CNT1_10                                  --订单外(日计)
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
   		 WHERE EXTSHAPE_DEC_DTIME >= ?||'000000'
		 AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
		 AND   EXTSHAPE_DEC_GRD = '3') CNT1_11                                  --等级外(日计)	
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
		 WHERE EXTSHAPE_DEC_DTIME >= ?||'000000'
		 AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
		 AND   EXTSHAPE_DEC_GRD = '4') CNT1_12                                  --废钢(日计)		
         ,SQ_QD_COMMON.COMMCD_FINDCRK('D',?,1) CNT1_3                        --缺陷1(日计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('D',?,2) CNT1_4                        --缺陷2(日计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('D',?,3) CNT1_5                        --缺陷3(日计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('D',?,4) CNT1_6                        --缺陷4(日计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('D',?,5) CNT1_7                        --缺陷5(日计)
    FROM DUAL
    UNION ALL
    SELECT    
         (SELECT COUNT(*) FROM TBH02_COIL_COMM
         WHERE EXTSHAPE_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
         AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
         AND   EXTSHAPE_DEC_GRD >= '1') CNT1_1                                  --外观判定钢卷数(月计)
         ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
         WHERE EXTSHAPE_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
         AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
         AND   EXTSHAPE_DEC_GRD = '1') CNT1_2                                  --合格钢卷数(月计)
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
		 WHERE EXTSHAPE_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
		 AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
		 AND   EXTSHAPE_DEC_GRD >= '2') CNT1_9                                  --不合格钢卷数(日计)	
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
		 WHERE EXTSHAPE_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
         AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
         AND   EXTSHAPE_DEC_GRD = '2') CNT1_10                                   --订单外(日计)
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
   		 WHERE EXTSHAPE_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
		 AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
		 AND   EXTSHAPE_DEC_GRD = '3') CNT1_11                                  --等级外(日计)	
		 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
		 WHERE EXTSHAPE_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
		 AND   EXTSHAPE_DEC_DTIME <= ?||'235959'
		 AND   EXTSHAPE_DEC_GRD = '4') CNT1_12                                  --废钢(日计)		
         ,SQ_QD_COMMON.COMMCD_FINDCRK('M',?,1) CNT1_3                        --缺陷1(月计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('M',?,2) CNT1_4                        --缺陷2(月计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('M',?,3) CNT1_5                        --缺陷3(月计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('M',?,4) CNT1_6                        --缺陷4(月计)
         ,SQ_QD_COMMON.COMMCD_FINDCRK('M',?,5) CNT1_7                        --缺陷5(月计)
    FROM DUAL
)
SELECT T.*
,DECODE(CNT1_1,0,'',to_char((CNT1_2/CNT1_1)*100,'990.99')||'%') AS CNT1_8       --合格率
FROM RESULT T					
		]]>	
	</query>
	<query  id="UIB020290_02.SELECT" desc="产品检验实绩">
		<![CDATA[
WITH RESULT AS (
SELECT 
     (SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD    >= '1') CNT1_1
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD = '1') CNT1_2
	 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2') CNT1_9		
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >=        '2'
     AND   INGR_DEC_GRD <> '1') CNT1_3
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD  >= '2'
     AND   MATLQLTY_DEC_GRD >= '2') CNT1_4
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2'
     AND   WGT_DEC_RST >= '2') CNT1_5
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2'
     AND   SIZE_DEC_RST >= '2') CNT1_6
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= ?||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2'
     AND   EXTSHAPE_DEC_GRD >= '2') CNT1_7
FROM DUAL
UNION ALL
SELECT    
     (SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD    >= '1') CNT1_1
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD = '1') CNT1_2
	 ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2') CNT1_9		
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >=        '2'
     AND   INGR_DEC_GRD <> '1') CNT1_3
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD  >= '2'
     AND   MATLQLTY_DEC_GRD >= '2') CNT1_4
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2'
     AND   WGT_DEC_RST >= '2') CNT1_5
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2'
     AND   SIZE_DEC_RST >= '2') CNT1_6
     ,(SELECT COUNT(*) FROM TBH02_COIL_COMM
     WHERE TOT_DEC_DTIME >= SUBSTR(?,1,6)||'01'||'000000'
     AND   TOT_DEC_DTIME <= ?||'235959'
     AND   TOT_DEC_GRD >= '2'
     AND   EXTSHAPE_DEC_GRD >= '2') CNT1_7
FROM DUAL    
)
SELECT T.*
,DECODE(CNT1_1,0,'',to_char((CNT1_2/CNT1_1)*100,'990.99')||'%') AS CNT1_8                            --合格率
FROM RESULT T			
		]]>	
	</query>
</queryMap>