XinYu_LZMES/.svn/pristine/72/72d582945d4bc34d8358f893edaa1b24704ccabe.svn-base

<?xml version="1.0" encoding='UTF-8'?>

<queryMap desc="酸轧轧制机组统计">


<query id="UIC030001_01.SELECT">
  <![CDATA[
		select '普钢' LJ,
       sum(to_number(round(t.ACT_WGT / 1000, 2))) CL,
       round(sum(decode(t.CUT_EDGE_YN,
                        'True',
                        to_number(round(t.ACT_WGT / 1000, 2)),
                        0)) / sum(to_number(round(t.ACT_WGT / 1000, 2))),
             2) QBBL,
       round(avg(t.COIL_THK), 2) pjhd,
       round(avg(t.COIL_WTH), 2) pjkd,
       round(avg(to_number(t.ACT_WGT / 1000)), 2) PJJZ
  from C_TBL02_COIL_COMM t
 where t.MILL_DTIME BETWEEN ? AND ?
   and t.SPEC_STL_GRD not like '%W%'
   and t.MILL_GROUP <> '0'
UNION ALL
select '硅钢' LJ,
       sum(to_number(round(t.ACT_WGT / 1000, 2))) CL,
       round(sum(decode(t.CUT_EDGE_YN,
                        'True',
                        to_number(round(t.ACT_WGT / 1000, 2)),
                        0)) / sum(to_number(round(t.ACT_WGT / 1000, 2))),
             2) QBBL,
       round(avg(t.COIL_THK), 2) pjhd,
       round(avg(t.COIL_WTH), 2) pjkd,
       round(avg(to_number(t.ACT_WGT / 1000)), 2) PJJZ
  from C_TBL02_COIL_COMM t
 where t.MILL_DTIME BETWEEN ? AND ?
   and t.SPEC_STL_GRD like '%W%'
   and t.MILL_GROUP <> '0'
UNION ALL
select '累计' LJ,
       sum(to_number(round(t.ACT_WGT / 1000, 2))) CL,
       round(sum(decode(t.CUT_EDGE_YN,
                        'True',
                        to_number(round(t.ACT_WGT / 1000, 2)),
                        0)) / sum(to_number(round(t.ACT_WGT / 1000, 2))),
             2) QBBL,
       round(avg(t.COIL_THK), 2) pjhd,
       round(avg(t.COIL_WTH), 2) pjkd,
       round(avg(to_number(t.ACT_WGT / 1000)), 2) PJJZ
  from C_TBL02_COIL_COMM t
 where t.MILL_DTIME BETWEEN ? AND ?
       and t.MILL_GROUP <> '0'
UNION ALL
select '平均日产' LJ,
       round(sum(to_number(round(t.ACT_WGT / 1000, 2)))/
       (TO_DATE(?,'YYYY/MM/DD')-TO_DATE(?,'YYYY/MM/DD'))) CL,
       to_number('') QBBL,
       to_number('') pjhd,
       to_number('') pjkd,
       to_number('') PJJZ
  from C_TBL02_COIL_COMM t
 where t.MILL_DTIME BETWEEN ? AND ?
 and t.MILL_GROUP <> '0' 
 
 ]]>
</query>

<query id="UIC030001_02.SELECT" desc="酸轧轧制机组">
  <![CDATA[
   
select T.SPEC_STL_GRD LJ,
       round(sum(t.ACT_WGT) / 1000, 2) CL,
       --ROUND(sum(t1.coil_wgt) / 1000, 2) coil_wgt,
       ROUND((sum(t.ACT_WGT) / sum(t1.coil_wgt)) * 100, 2) || '%' ccl,
       SUM(T2.ZZSJ) ZZSJ,
       round(round(sum(t.ACT_WGT) / 1000, 2) / SUM(T2.ZZSJ), 2) * 60 JSCL,
       sum(t.COIL_LEN / 1000) COIL_LEN,
       round(sum(t.COIL_LEN / 1000) / SUM(T2.ZZSJ), 2) ZZSD
  from (select T.SPEC_STL_GRD,
               sum(t.ACT_WGT) act_wgt,
               SUBSTR(t.COIL_NO, 1, 11) coil_no,
               MAX(t.COIL_LEN) COIL_LEN
          from C_TBL02_COIL_COMM t
         where t.MILL_GROUP <> 0
           and t.ACT_WGT is not null
              --   and t.SPEC_STL_GRD = '50XW1300'
           and t.MILL_DTIME BETWEEN ? AND ?
         group by SUBSTR(t.COIL_NO, 1, 11), t.SPEC_STL_GRD) t,
       C_TBF03_SPEC_MILL t1,
       (select substr(t.L_COIL_NO, 1, 11) coil_no,
               t1.SPEC_STL_GRD,
               sum(ROUND(to_number((to_date(t.ROLLING_END1,
                                            'yyyy-mm-dd hh24:mi:ss') -
                                   to_date(T.ROLLING_BEGIN1,
                                            'yyyy-mm-dd hh24:mi:ss'))) * 24 * 60,
                         2)) zzsj
          from C_TBL02_PLTCM_RESULT t, c_tbl02_coil_comm t1
         where t1.COIL_NO = t.L_COIL_NO
           and t1.MILL_DTIME BETWEEN ? AND ?
           and (t.ROLLING_END1 not like '%年%' or T.ROLLING_BEGIN1 not like '%年%')
           and (t.ROLLING_END1 not like '%月%' or T.ROLLING_BEGIN1 not like '%月%')
           and (t.ROLLING_END1 not like '%日%' or T.ROLLING_BEGIN1 not like '%日%')
        --and t1.SPEC_STL_GRD = '50XW1300'
         group by t1.SPEC_STL_GRD, substr(t.L_COIL_NO, 1, 11)) T2
 where SUBSTR(t1.c_coil_no, 1, 11) = t.COIL_NO
   AND t.COIL_NO = T2.coil_no
 group by T.SPEC_STL_GRD
union all
select '累计' LJ,
       round(sum(t.ACT_WGT) / 1000, 2) CL,
       --ROUND(sum(t1.coil_wgt) / 1000, 2) coil_wgt,
       ROUND((sum(t.ACT_WGT) / sum(t1.coil_wgt)) * 100, 2) || '%' ccl,
       SUM(T2.ZZSJ) ZZSJ,
       round(round(sum(t.ACT_WGT) / 1000, 2) / SUM(T2.ZZSJ), 2) * 60 JSCL,
       sum(t.COIL_LEN / 1000) COIL_LEN,
       round(sum(t.COIL_LEN / 1000) / SUM(T2.ZZSJ), 2) ZZSD
  from (select T.SPEC_STL_GRD,
               sum(t.ACT_WGT) act_wgt,
               SUBSTR(t.COIL_NO, 1, 11) coil_no,
               MAX(t.COIL_LEN) COIL_LEN
          from C_TBL02_COIL_COMM t
         where t.MILL_GROUP <> 0
           and t.ACT_WGT is not null
              --and t.SPEC_STL_GRD = '50XW1300W'
           and t.MILL_DTIME BETWEEN ? AND ?
         group by SUBSTR(t.COIL_NO, 1, 11), t.SPEC_STL_GRD) t,
       C_TBF03_SPEC_MILL t1,
       (select substr(t.L_COIL_NO, 1, 11) coil_no,
               t1.SPEC_STL_GRD,
               sum(ROUND(to_number((to_date(t.ROLLING_END1,
                                            'yyyy-mm-dd hh24:mi:ss') -
                                   to_date(T.ROLLING_BEGIN1,
                                            'yyyy-mm-dd hh24:mi:ss'))) * 24 * 60,
                         2)) zzsj
          from C_TBL02_PLTCM_RESULT t, c_tbl02_coil_comm t1
         where t1.COIL_NO = t.L_COIL_NO
           and t1.MILL_DTIME BETWEEN ? AND ?
           and (t.ROLLING_END1 not like '%年%' or T.ROLLING_BEGIN1 not like '%年%')
           and (t.ROLLING_END1 not like '%月%' or T.ROLLING_BEGIN1 not like '%月%')
           and (t.ROLLING_END1 not like '%日%' or T.ROLLING_BEGIN1 not like '%日%')
        --and t1.SPEC_STL_GRD = '50XW1300W'
         group by t1.SPEC_STL_GRD, substr(t.L_COIL_NO, 1, 11)) T2
 where SUBSTR(t1.c_coil_no, 1, 11) = t.COIL_NO
   AND t.COIL_NO = T2.coil_no
   
   
 ]]>
</query>

</queryMap>