XinYu_LG2MES/ServerSolution/WebRoot/WEB-INF/query - 副本/.svn/text-base/QueryPullRate.xml.svn-base

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="作业指令处理类">

	<query id="PullRate.Query" desc="拉率数据计算依据" fetchSize="10">
		<![CDATA[
SELECT T.HEATNO,
       T.STATIONCODE,
       T.SHIFTCODE,
       Y.FACT_ROUTE,
       nvl(Z.S1CASTSPEED, 0) S1CASTSPEED,
       nvl(Z.S2CASTSPEED, 0) S2CASTSPEED,
       nvl(Z.S3CASTSPEED, 0) S3CASTSPEED,
       nvl(Z.S4CASTSPEED, 0) S4CASTSPEED,
       (select SLABWIDTH || '*' || SLABHEIGHT
          from PPC_STEEL_CUTINGDETAIL
         where billet_xn like '3' || SUBSTR(T.HEATNO, 2) || '%'
           and rownum < 2) Aim_W_T, 
       NVL(T.MWRAPTEMPT, 0) MWRAPTEMPT, 
       T.PLANSTEEL, 
       NVL(LMLTEMPT, 0) LMLTEMPT, 
       to_char(T.OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'ALL' PRIVATEV
  FROM (SELECT HEATNO,
               OPTDATE,
               SHIFTCODE,
               STATIONCODE,
               MWRAPTEMPT,
               PLANSTEEL,
               LMLTEMPT
          FROM STL_CCM_OPTINFO
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?
        UNION
        SELECT HEATNO,
               OPTDATE,
               SHIFTCODE,
               STATIONCODE,
               MWRAPTEMPT,
               PLANSTEEL,
               LMLTEMPT
          FROM J#STL_CCM_OPTINFO
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?) T,
       (SELECT HEATNO, FACT_ROUTE FROM PPC_STEEL_HEAT) Y,
       (SELECT HEATNO, S1CASTSPEED, S2CASTSPEED, S3CASTSPEED, S4CASTSPEED
          FROM STL_CCM_F1SAMPLING
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?
        UNION
        SELECT HEATNO, S1CASTSPEED, S2CASTSPEED, S3CASTSPEED, S4CASTSPEED
          FROM STL_CCM_F2SAMPLING
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?
        UNION
        SELECT HEATNO, S1CASTSPEED, S2CASTSPEED, S3CASTSPEED, S4CASTSPEED
          FROM STL_CCM_F3SAMPLING
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?
        UNION
        SELECT HEATNO, S1CASTSPEED, S2CASTSPEED, S3CASTSPEED, S4CASTSPEED
          FROM J#STL_CCM_F1SAMPLING
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?
        UNION
        SELECT HEATNO, S1CASTSPEED, S2CASTSPEED, S3CASTSPEED, S4CASTSPEED
          FROM J#STL_CCM_F2SAMPLING
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?
        UNION
        SELECT HEATNO, S1CASTSPEED, S2CASTSPEED, S3CASTSPEED, S4CASTSPEED
          FROM J#STL_CCM_F3SAMPLING
         WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?) Z
 WHERE T.HEATNO = Z.HEATNO(+)
   AND T.HEATNO = Y.HEATNO(+)
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'CCM' PRIVATEV
  FROM STL_CCM_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'CCM' PRIVATEV
  FROM J#STL_CCM_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ? ---连铸机作业操作记录表
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'BOF' PRIVATEV
  FROM STL_BOF_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'BOF' PRIVATEV
  FROM J#STL_BOF_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ? ---转炉作业实绩
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'CAS' PRIVATEV
  FROM STL_CAS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'CAS' PRIVATEV
  FROM J#STL_CAS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'LFS' PRIVATEV
  FROM STL_LFS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'LFS' PRIVATEV
  FROM J#STL_LFS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'RHS' PRIVATEV
  FROM STL_RHS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'RHS' PRIVATEV
  FROM J#STL_RHS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'VDS' PRIVATEV
  FROM STL_VDS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       '' FACT_ROUTE,
       0 S1CASTSPEED,
       0 S2CASTSPEED,
       0 S3CASTSPEED,
       0 S4CASTSPEED,
       '' Aim_W_T, 
       0 MWRAPTEMPT, 
       '' PLANSTEEL, 
       0 LMLTEMPT, 
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       'VDS' PRIVATEV
  FROM J#STL_VDS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?

	 ]]>
	</query>


	<query id="Temperature.Query" desc="温度数据计算依据" fetchSize="10">
		<![CDATA[
		SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'BOF' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(POTTEMP,0) TEMPERATURE 
  FROM STL_BOF_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'BOF' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(POTTEMP,0) TEMPERATURE 
  FROM J#STL_BOF_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'CAS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(ARITEMPT,0) TEMPERATURE 
  FROM STL_CAS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'CAS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(ARITEMPT,0) TEMPERATURE 
  FROM J#STL_CAS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'LFS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(LEAVETEMPT,0) TEMPERATURE
  FROM STL_LFS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'LFS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(LEAVETEMPT,0) TEMPERATURE
  FROM J#STL_LFS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'RHS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(ARBTEMPT,0) TEMPERATURE 
  FROM STL_RHS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'RHS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(ARBTEMPT,0) TEMPERATURE 
  FROM J#STL_RHS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'VDS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(ARBTEMPT,0) TEMPERATURE
  FROM STL_VDS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'VDS' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(ARBTEMPT,0) TEMPERATURE
  FROM J#STL_VDS_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'CCM' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(MOULDBALETEMPT,0) TEMPERATURE
  FROM STL_CCM_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?
UNION
SELECT HEATNO,
       STATIONCODE,
       SHIFTCODE,
       LMLTEMPT,
       'CCM' PRIVATEV,
       to_char(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       NVL(MOULDBALETEMPT,0) TEMPERATURE
  FROM J#STL_CCM_OPTINFO
 WHERE to_char(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND ?

		]]>
	</query>
	
	<query id="CCMCasting.Query" desc="连铸浇铸保护报表依据" fetchSize="10">
		<![CDATA[
		SELECT T.HEATNO,
       TO_CHAR(OPTDATE, 'YYYY-MM-DD') OPTDATE,
       STATIONCODE,
       SHIFTCODE,
       NVL(N, 0) N,
       NVL(Als, 0) Als,
       Y.FACT_ROUTE,
       NVL((SELECT N
             FROM (SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM J#STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'D%'
              AND ROWNUM < 2),
           0) DNS,
       NVL((SELECT Als
             FROM (SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM J#STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'D%'
              AND ROWNUM < 2),
           0) DAls,
       NVL((SELECT N
             FROM (SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM J#STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'E%'
              AND ROWNUM < 2),
           0) ENS,
       NVL((SELECT Als
             FROM (SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM J#STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'E%'
              AND ROWNUM < 2),
           0) EAls,
       NVL((SELECT N
             FROM (SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM J#STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'F%'
              AND ROWNUM < 2),
           0) FNS,
       NVL((SELECT Als
             FROM (SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'F%'
              AND ROWNUM < 2),
           0) FAls,
       NVL((SELECT N
             FROM (SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT N, HEATNO, STATIONCODE, ASSAYDATE
                     FROM J#STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'I%'
              AND ROWNUM < 2),
           0) INS,
       NVL((SELECT Als
             FROM (SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM STL_CHEMELEMENT
                   UNION
                   SELECT Als, HEATNO, STATIONCODE, ASSAYDATE
                     FROM J#STL_CHEMELEMENT
                    ORDER BY ASSAYDATE DESC) V
            WHERE V.HEATNO = T.HEATNO
              AND V.STATIONCODE LIKE 'I%'
              AND ROWNUM < 2),
           0) IAls
  FROM (SELECT HEATNO, STATIONCODE, SHIFTCODE, OPTDATE
          FROM STL_CCM_OPTINFO
         WHERE TO_CHAR(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?
        UNION
        SELECT HEATNO, STATIONCODE, SHIFTCODE, OPTDATE
          FROM J#STL_CCM_OPTINFO
         WHERE TO_CHAR(OPTDATE, 'YYYY-MM-DD') BETWEEN ? AND
               ?) T,
       (SELECT MIN(N) N, MIN(Als) Als, HEATNO, MAX(ASSAYDATE)
          FROM (SELECT N, Als, HEATNO, ASSAYDATE
                  FROM STL_CHEMELEMENT
                 WHERE STATIONCODE LIKE 'G%'
                UNION
                SELECT N, Als, HEATNO, ASSAYDATE
                  FROM J#STL_CHEMELEMENT
                 WHERE STATIONCODE LIKE 'G%')
         GROUP BY HEATNO) S,
       (SELECT HEATNO, FACT_ROUTE
          FROM PPC_STEEL_HEAT
        UNION
        SELECT HEATNO, FACT_ROUTE
          FROM J#PPC_STEEL_HEAT
         WHERE TO_CHAR(PLAN_MAKING_TIME, 'YYYY-MM-DD') BETWEEN ? AND
               ?) Y
 WHERE T.HEATNO = S.HEATNO(+)
    AND T.HEATNO = Y.HEATNO(+)
 ORDER BY stationcode
		]]>
	</query>
	
	<query id="CCMCastingdrg.Query" desc="连铸浇铸保护报表子界面查询" fetchSize="10">
		<![CDATA[
	   select a.heatno "熔炼号",       c.STL_GRD "计划牌号",
       b.ASSAYTYPECODE,       round(Mn / S, 4) SIMN,
       to_char(b.ASSAYDATE, 'yyyy-MM-dd HH24:mi:ss') "化验时间",
       C,       Mn,       S,       P,
       Si,       Ni,       Cr,       Cu,
       Asn,       Ti,       V,       N,       H,
       O,       Alt,       Als,       Pcm,       CEV
  from (select *
          from STL_BOF_OPTINFO
        union
        select * from J#STL_BOF_OPTINFO) a
  left join (select * from stl_judge_result) c on c.charge_no = a.heatno
  left join (select *
               from ppc_steel_heat
             union
             select * from j#ppc_steel_heat) d on 'J' ||
                                                  SUBSTR(d.v_heat_id, 2, 9) =
                                                  a.heatno,
 (select *
                                                     from STL_CHEMELEMENT
                                                   union
                                                   select *
                                                     from J#STL_CHEMELEMENT) b
 where b.HEATNO = a.HEATNO
   $
 order by b.HEATNO,a.optdate desc, b.ASSAYTYPECODE desc
		]]>
	</query>
	
</queryMap>