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

<?xml version="1.0" encoding="UTF-8"?>
<queryMap desc="脱硫作业">    
    <query id="GetHmpOptInfo.Select" desc="查询脱硫作业信息" fetchSize="10">
        <![CDATA[
			        SELECT HEATPROCESSNO,
					       HUDWID,
					       EQUIPMENTNO,
					       WSID,
					       HEATNO,
					       PLANSTEEL,
					       ARRIVETIME,
					       LEAVETIME,
					       INSTATIONTIME,
					       SWTIME,
					       WHISKBEGINTIME,
					       WHISKCLOSETIME,
					       SB_FIRST_STARTIME,
					       SB_FIRST_ENDTIME,
					       SB_FIRST_TIME,
					       FIRSTQUANTITY,
					       SB_SECOND_STARTIME,
					       SB_SECOND_ENDTIME,
					       SB_SECOND_TIME,
					       SECONDQUANTITY,
					       SB_THIRD_STARTIME,
					       SB_THIRD_ENDTIME,
					       SB_THIRD_TIME,
					       THIRDQUANTITY,
					       CHURNUPDATE,
					       SETCHURNUPDATE,
					       DR_BEF_STARTTIME,
					       DR_BEF_ENDTIME,
					       DR_BEF_TIME,
					       DR_BEF_WGT,
					       DR_AFT_STARTTIME,
					       DR_AFT_ENDTIME,
					       DR_AFT_TIME,
					       DR_AFT_WGT,
					       DREFFECT,
					       DFRSTARTTIME,
					       DFRENDTIME,
					       DFRTIME,
					       SWFELEMENTCODE,
					       SWFTEMPERATURE,
					       SWBELEMENTCODE,
					       SWBTEMPERATURE,
					       SWBSPERCENT,
					       IRONPOTGROSSWGT,
					       IRONPOTTAREWGT,
					       IRONWGT,
					       ARRIVEWGTTIME,
					       LEAVEWGTTIME,
					       ARRIVEWGT,
					       LEAVEWGT,
					       IRONPOTBOFWGT,
					       PRETEMPERATURE,
					       BLOWSPEED,
					       MOSTROTATESPEED,
					       IRONPWRAPNO,
					       POTAGE,
					       MIX_REALITY_HEIGHT,
					       MR_USER_TIMES,
					       IRONSOURCE,
					       MOLIRNLEAVE,
					       RSSTOVENO,
					       RSWGT,
					       SBPRESS,
					       SBPLUX,
					       SBCONSUME,
					       CAOCONSUME,
					       SCONTENT,
					       N2PLUX,
					       N2CONSUME,
					       N2PRESS,
					       SGGNUMID,
					       SGGAGE,
					       SGGNUMNO,
					       SIRONWGT,
					       VFLAG,
					       EXECUTESTATE,
					       DFRSTATE,
					       DFRWGT,
					       RESERVSECANPRESSURE,
					       VALVEOPENWIDTH,
					       DRQUANTITY,
					       NUMID,
					       FORMER_S_VALUE,
					       DREGSSOLVENT,
					       SITH_S_VALUE,
					       SHIFTCODE,
					       OPTPERSONNEL,
					       OPTDATE,
					       UPDATETIME,
					       MEMO
					  FROM STL_HMP_OPTINFO
					 WHERE HEATPROCESSNO = ?
					   AND EQUIPMENTNO = ?					  
	 ]]>
    </query>   
    <query id="Getdeslagidfromresults.Select" desc="获取最新脱硫处理号" fetchSize="10">
        <![CDATA[
			 select mes_lg_cmdchg.getdeslagidfromresults (?) HudwID  from dual       				  
	 ]]>
    </query>  
    
    <query id="GetOverHmpdate.Select" desc="查询脱硫完成信息" fetchSize="10">
        <![CDATA[
			  	select HUDWID,
				       MOLIRNLEAVE,
				       IRONPWRAPNO,       
				       DFRSTARTTIME,
				       DFRENDTIME,
				       DFRTIME,
				       DFRWGT,
				       round(FRONTTEMPERATURE) FRONTTEMPERATURE,
				       SWBTEMPERATURE,
				       IRONWGT,   
				       ARRIVEWGTTIME,
				       ARRIVEWGT,
				       LEAVEWGTTIME,
				       LEAVEWGT,  
				       RSSTOVENO,
				       RSWGT,
				       SWFTEMPERATURE,
				       IRONSOURCE
				  from (select *
				          from STL_HMP_OPTINFO
				        union
				        select * from J#STL_HMP_OPTINFO)
				 where to_char(DFRENDTIME, 'YYYYMMDDHH24MISS') BETWEEN
				       to_char((sysdate - 1 / 4), 'YYYYMMDDHH24MISS') and
				       to_char(sysdate, 'YYYYMMDDHH24MISS')
				   and EQUIPMENTNO = ?
				 order by HUDWID desc			  
	 ]]>
    </query>   
    
    <query id="GetCVTHMPCurrentHeatNo.Select" desc="查询脱硫工序和转炉工序正生产的炉号信息" fetchSize="10">
        <![CDATA[
			   select t.objectpos objectpos,
				       decode(substr(t.objectid, 1, 1),
				              'B',
				              (select HUDWID
				                 from stl_hmp_optinfo
				                where HEATPROCESSNO = t.objectid),
				              t.objectid) objectid
				  from tpm_trackobjectinfor t
				 where (t.objectpos like upper('bof0%_DEVICE') or
				       t.objectpos like upper('bof0%_BACK') OR
				       t.objectpos like upper('hmp0%_DEVICE') or
				       t.objectpos like upper('hmp0%_FRONT'))
				   and t.objectid is not null			  
	 ]]>
    </query>   
    
    <query id="ExistSendIronToBof" desc="判断是否已发送铁水信息" fetchSize="10">
        <![CDATA[
			    select Count(*) intCount
				  from (select heatno
				          from stl_hmp_optinfo
				         where heatno = ?
				        union
				        select heatno from J#stl_hmp_optinfo where heatno = ?)
	  
	 ]]>
    </query>   
    
    <query id="SendHmpIronToBof.Update" desc="脱硫发送铁水信息到转炉" fetchSize="10">
        <![CDATA[
			   job_lg_manage.SendHmpIronToBof	  
	 ]]>
    </query>   
    
    <query id="ReceiveHmpCraftElement.Select" desc="查询脱硫作业铁水化学成分信息" fetchSize="10">
        <![CDATA[
			    select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from stl_CHEMELEMENT
				 where heatno = ?
				   and substr(checkno, length(checkno), 1) = ?
				UNION
				select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from j#stl_CHEMELEMENT
				 where heatno = ?
				   and substr(checkno, length(checkno), 1) = ?
				UNION
				select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from stl_CHEMELEMENT
				 where heatno =
				       (select IRONSOURCE from stl_hmp_optinfo where HUDWID = ?)
				   and substr(checkno, length(checkno), 1) = ?
				union
				select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from j#stl_CHEMELEMENT
				 where heatno =
				       (select IRONSOURCE from stl_hmp_optinfo where HUDWID = ?)
				   and substr(checkno, length(checkno), 1) = ?
				 order by ASSAYDATE desc
	 ]]>
    </query>   
    
    <query id="ReceiveHmpOverElement.Select" desc="查询脱硫完铁水化学成分信息" fetchSize="10">
        <![CDATA[
			    select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from stl_CHEMELEMENT
				 where heatno = ?
				   and substr(checkno, length(checkno), 1) = ?
				UNION
				select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from j#stl_CHEMELEMENT
				 where heatno = ?
				   and substr(checkno, length(checkno), 1) = ?
				UNION
				select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from stl_CHEMELEMENT
				 where heatno =
				       (select IRONSOURCE from (select * from stl_hmp_optinfo union select * from J#stl_hmp_optinfo) where HUDWID = ?)
				   and substr(checkno, length(checkno), 1) = ?
				union
				select CHECKNO, heatno, c, si, mn, p, s, asn, ni, Cu, sn, sb, ASSAYDATE
				  from j#stl_CHEMELEMENT
				 where heatno =
				       (select IRONSOURCE from (select * from stl_hmp_optinfo union select * from J#stl_hmp_optinfo) where HUDWID = ?)
				   and substr(checkno, length(checkno), 1) = ?
				 order by ASSAYDATE desc
	 ]]>
    </query>  
    
  
     <query id="GetBfIronInfo.Select" desc="查询高炉铁水信息" fetchSize="10">
        <![CDATA[
				select t.id_,
       t.stoveid,
       t.stovetime,
       t.tapholeno,
       t.tfpotid,
       t.tfpottarewgt,
       t.tfpotgrosswgt,
       t.tfpotwgt,
       t.tfirontemp,
       nvl(a.heatno, b.heatno) heatno,
       nvl(a.checkno, b.checkno) checkno,
       nvl(a.c, b.c) c,
       nvl(a.mn, b.mn) mn,
       nvl(a.s, b.s) s,
       nvl(a.p, b.p) p,
       nvl(a.si, b.si) si,
       nvl(a.SN, b.SN) SN,
       nvl(a.ASn, b.ASn) "AS",
       nvl(a.Cu, b.Cu) Cu,
       nvl(a.Cr, b.Cr) Cr,
       t.status,
       to_char(t.hstapholetime, 'yyyy-MM-dd HH24:mi:ss') hstapholetime,
       to_char(nvl(a.assaydate, b.assaydate), 'yyyy-MM-dd HH24:mi:ss') assaydate
  from stl_if_blastfurnaceiron t
  left join stl_chemelement a on a.heatno = lpad(t.stoveid, 2, '0') || '-' ||
                                 lpad(t.stovetime, 6, '0')
  left join J#stl_chemelement b on b.heatno = lpad(t.stoveid, 2, '0') || '-' ||
                                   lpad(t.stovetime, 6, '0')
 where to_char(hstapholetime, 'YYYYMMDDHH24MISS') BETWEEN
       to_char((sysdate - 1 / 3), 'YYYYMMDDHH24MISS') and
       to_char(sysdate, 'YYYYMMDDHH24MISS')
   and t.stoveid = decode(?, '0', t.stoveid, ?)
 order by t.hstapholetime desc

	 ]]>
    </query>   
    
    <query id="InsertBFIronData.Insert" desc="添加高炉铁水铁运进铁实绩" fetchSize="10">
        <![CDATA[
			 job_lg_manage.BFIronComeIn_AddNew       				  
	 ]]>
    </query>  
    
     <query id="UpdateBFIronData.Update" desc="更新高炉铁水铁运进铁实绩" fetchSize="10">
        <![CDATA[
			 job_lg_manage.BFIRONCOMEIN_UPDATENEW       				  
	 ]]>
    </query>  
    
     <query id="DeleteBFIronData.Delete" desc="删除高炉铁水铁运进铁实绩" fetchSize="10">
        <![CDATA[
			 job_lg_manage.BFIronComeIn_Delete       				  
	 ]]>
    </query>  
    
    <query id="GetInIronData.Select" desc="查询铁运进铁铁水信息" fetchSize="10">
        <![CDATA[
		        select misid,
				       stoveid,
				       stovetime,
				       tapholeno,
				       tfpotid,
				       tfpotgrosswgt,
				       tfpottarewgt,
				       tfpotwgt,
				       irontemperature,
				       t.checkno,
				       t.c,
				       t.si,
				       t.mn,
				       t.p,
				       t.s,
				       t.SN,t.ASn "As",t.Cu,t.Cr,
				       t.hstapholetime,
				       t.arrivetime,
				       t.shiftcode,
				       optpersonnel,
				       operationdate,
				       memo,
				       molirnsource,
				       nvl(a.c,b.c) C_P,
               		   nvl(a.si,b.si) SI_P,
               		   nvl(a.mn,b.mn) MN_P,
               		   nvl(a.p,b.p) P_P,
               		   nvl(a.s,b.s) S_P,
               		   nvl(a.SN,b.SN) SN_P,
               		   nvl(a.ASn,b.ASn) AS_P,
               		   nvl(a.Cu,b.Cu) Cu_P,
               		   nvl(a.Cr,b.Cr) Cr_P,
				       RSWGT,
				       t.BFIRONTEMTURE
				  from stl_mis_importiron t
				       left join stl_chemelement a on t.misid = a.heatno
          left join j#stl_chemelement b on t.misid = b.heatno
				 where to_char(arrivetime, 'yyyy-MM-dd HH24:mi:ss') >= ?
				   and to_char(arrivetime, 'yyyy-MM-dd HH24:mi:ss') <= ?
				   and substr(t.shiftcode, 2, 1) = decode(?, '0', substr(t.shiftcode, 2, 1), ?)				   
				   and decode(?, '', '0', stoveid) = decode(?, '', '0', ?)
				 order by misid desc
	 ]]>
    </query>  
    
    <query id="GetIronMolirnsourceData.Select" desc="查询铁运进铁铁水信息" fetchSize="10">
        <![CDATA[
		        select misid,
				       stoveid,
				       stovetime,
				       tapholeno,
				       tfpotid,
				       tfpotgrosswgt,
				       tfpottarewgt,
				       tfpotwgt,
				       irontemperature,				     
				       c,
				       si,
				       mn,
				       p,
				       s,
				       hstapholetime,
				       arrivetime,
				       operationdate,				      
               	       molirnsource,
               	       RSWGT,
               	       BFIRONTEMTURE
				  from stl_mis_importiron
				 where to_char(arrivetime, 'yyyy-MM-dd HH24:mi:ss') >= ?				       
				   and to_char(arrivetime, 'yyyy-MM-dd HH24:mi:ss') <= ?
				    order by misid desc
	 ]]>
    </query>  
    
     <query id="HmpJobIronMolirnSource.Update" desc="删除高炉铁水铁运进铁实绩" fetchSize="10">
        <![CDATA[
			 job_lg_manage.HmpJobIronMolirnSource       				  
	 ]]>
    </query>  
    
     <query id="GetIronSendData.Select" desc="获取铁水发送数据" fetchSize="10">
        <![CDATA[
			   select a.*,
				       max(case
				             when a.HUDWID = b.HEATNO then
				              b.Mn
				           end) over(partition by a.HUDWID) MN,
				       max(case
				             when a.HUDWID = b.HEATNO then
				              b.si
				           end) over(partition by a.HUDWID) SI,
				       max(case
				             when a.HUDWID = b.HEATNO then
				              b.P
				           end) over(partition by a.HUDWID) P,
				       max(case
				             when a.HUDWID = b.HEATNO then
				              b.S
				           end) over(partition by a.HUDWID) S
				
				  from (select '0' FLAG,
				               HEATPROCESSNO,
				               EQUIPMENTNO,
				               HUDWID,
				               MOLIRNLEAVE,
				               IRONPWRAPNO,
				               CASE
				                  when nvl(t.LEAVEWGT, 0) > 0 and nvl(t.ARRIVEWGT, 0) > 0 and
				                       nvl(t.LEAVEWGT, 0) < nvl(t.ARRIVEWGT, 0) THEN
				                   round(t.IRONWGT -
				                         (nvl(t.ARRIVEWGT, 0) - nvl(t.LEAVEWGT, 0)),
				                         2)
				                  ELSE
				                   t.IRONWGT
				               END LEAVEWGT,
				               DFRSTARTTIME,
				               DFRENDTIME,
				               DFRTIME,
				               SWBTEMPERATURE,
				               LEAVEWGTTIME,
				               IRONSOURCE
				          from (select *
				                  from STL_HMP_OPTINFO
				                union
				                select * from J#STL_HMP_OPTINFO) t,
				               dev_ib_base_info t1
				         where to_char(DFRENDTIME, 'YYYYMMDDHH24MISS') BETWEEN
				               to_char((sysdate - 1/4), 'YYYYMMDDHH24MISS') and
				               to_char(sysdate, 'YYYYMMDDHH24MISS')
				           and t.ironpwrapno = t1.ladleid(+)
				           and (Length(nvl(MOLIRNLEAVE, ' ')) = 1 or
				                Length(nvl(MOLIRNLEAVE, ' ')) = ?)
				        union
				        select '0' FLAG,
				               '' HEATPROCESSNO,
				               '03' EQUIPMENTNO,
				               MISID HUDWID,
				               MOLIRNSOURCE MOLIRNLEAVE,
				               TFPOTID IRONPWRAPNO,
				               TFPOTWGT LEAVEWGT,
				               HSTAPHOLETIME DFRSTARTTIME,
				               ARRIVETIME DFRENDTIME,
				               round((ARRIVETIME - HSTAPHOLETIME) * 1440) DFRTIME,
				               round(IRONTEMPERATURE) SWBTEMPERATURE,
				               OPERATIONDATE LEAVEWGTTIME,
				               (STOVEID || STOVETIME || TFPOTID) IRONSOURCE
				          FROM stl_mis_importiron t
				         where to_char(OPERATIONDATE, 'YYYYMMDDHH24MISS') BETWEEN
				               to_char((sysdate - 1/4), 'YYYYMMDDHH24MISS') and
				               to_char(sysdate, 'YYYYMMDDHH24MISS')
				           and (select Count(IRONSOURCE)
				                  from (select *
				                          from stl_hmp_optinfo
				                        union
				                        select * from J#stl_hmp_optinfo) t
				                 where IRONSOURCE = MISID) = 0
				           and (Length(nvl(MOLIRNSOURCE, ' ')) = 1 or
				                Length(nvl(MOLIRNSOURCE, ' ')) = ? or
				                Length(nvl(MOLIRNSOURCE, ' ')) = ?)) a,
				       (select y.*
				          from (select x.*,
				                       row_number() over(partition by x.HEATNO order by x.ASSAYTYPECODE desc, x.assaydate desc) xh
				                  from (select *
				                          from STL_CHEMELEMENT
				                         where upper(SUBSTR(CHECKNO, -1)) = 'I'
				                           and upper(SUBSTR(CHECKNO, 1, 1)) = 'K'
				                        union
				                        select *
				                          from J#STL_CHEMELEMENT
				                         where upper(SUBSTR(CHECKNO, -1)) = 'I'
				                           and upper(SUBSTR(CHECKNO, 1, 1)) = 'K') x) y
				         WHERE Y.xh = 1) b
				 where a.HUDWID = b.heatno(+)
				 order by dfrstarttime desc

	 ]]>
    </query>  
      <query id="GetPlanHeatNoData.Select" desc="获取计划炉次数据" fetchSize="10">
        <![CDATA[
				  select '0' FLAG,
					       SUBSTR(HEATNO, 3, 1) STATIONCODE,
					       HEATNO,
					       AIM_GRADE_CODE STEELNAME
					  from ppc_steel_heat
					 where heat_state < '05'
					UNION
					SELECT '0' FLAG, SUBSTR(HEATNO, 3, 1) STATIONCODE, HEATNO, '' STEELNAME
					  FROM (select substr(heatno, 3, 1), max(heatno) HEATNO
					          from ppc_steel_heat
					         group by substr(heatno, 3, 1)) T
					 WHERE (SELECT COUNT(*)
					          FROM ppc_steel_heat
					         WHERE HEATNO = T.HEATNO
					           AND heat_state < '05') = 0
					 order by HEATNO
  	    ]]>
    </query>  
   
    <query id="SendIronDataBof.Send" desc="发送铁水信息到转炉模型" fetchSize="10">
        <![CDATA[
			 MES_LG_SENDEVENTRESTOMES.SendHmpIronResMes       				  
	 ]]>
    </query>  
    
    <query id="CancelIronSendData.Send" desc="取消铁水发送实绩" fetchSize="10">
        <![CDATA[
			 MES_LG_SENDEVENTRESTOMES.CancelIronSendData       				  
	 ]]>
    </query>  
    
     <query id="CancelHmpJobInfo.Cancel" desc="取消误加载的脱硫作业信息" fetchSize="10">
        <![CDATA[
			 job_lg_manage.CancelHmpJobInfo       				  
	 ]]>
    </query>  
    
     <query id="GetPTIronTemp.Select" desc="获取平台铁水温度数据" fetchSize="10">
        <![CDATA[
			  select heatprocessno, samplingvalue, samplingdate, flag
				  from stl_hmp_tempsampling
				 where flag = '0'
				   and to_char(samplingdate, 'YYYYMMDDHH24MISS') BETWEEN
				       to_char((sysdate - 1 / 4), 'YYYYMMDDHH24MISS') and
				       to_char(sysdate, 'YYYYMMDDHH24MISS')
				 order by samplingdate desc

	 ]]>
    </query>  
</queryMap>