XinYu_LG2MES/ClientSolution/Core.LgMes.Client.lgServiceMgt/LgSqlCollection/LgReportSql/SqlCollectionBof.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreFS.CA06;
using System.Data;
using System.Collections;
using Core.Mes.Client.Common;

namespace Core.LgMes.Client.lgServiceMgt.LgSqlCollection.LgReportSql
{
    public class SqlCollectionBof
    {

        /// <summary>
        /// 查询转炉炉前铁水信息
        /// </summary>
        /// <param name="sqlConditionList"></param>
        /// <returns></returns>
        public static string returnSqlOfBofForwardReport(ArrayList sqlConditionList)
        {
            string sqlStr = "";
            sqlStr = @"select distinct t.HEATNO,    --炉次号
                                        t.IRONPOTID, --铁水编号
                                        t4.aim_grade_code PLANSTEEL,      --钢种计划
                                        nvl(t.IRONPOTWGT, '0') IRONPOTWGT,--铁水重量
                                        t.IRONTEMPERATURE,                --铁水温度
                                        case substr(t.SHIFTCODE, 2, 1)    --班组
                                            when '1' then
                                             '甲'
                                            when '2' then
                                             '乙'
                                            when '3' then
                                             '丙'
                                            when '4' then
                                             '丁'
                                         end BB,
                                        case substr(t.SHIFTCODE, 1, 1)   --班次
                                           when '1' then
                                            '早'
                                           when '2' then
                                            '中'
                                           when '3' then
                                            '夜'
                                         end BC,
                                        --入炉铁水信息
                                        max(case
                                              when t.MOLIRNSOURCE = t10.HEATNO then
                                               t10.Mn
                                            end) over(partition by t.MOLIRNSOURCE) R_MN,
                                        max(case
                                              when t.MOLIRNSOURCE = t10.HEATNO then
                                               t10.C
                                            end) over(partition by t.MOLIRNSOURCE) R_C,
                                        max(case
                                              when t.MOLIRNSOURCE = t10.HEATNO then
                                               t10.P
                                            end) over(partition by t.MOLIRNSOURCE) R_P,
                                        max(case
                                              when t.MOLIRNSOURCE = t10.HEATNO then
                                               t10.S
                                            end) over(partition by t.MOLIRNSOURCE) R_S,
                                        max(case
                                              when t.MOLIRNSOURCE = t10.HEATNO then
                                               t10.si
                                            end) over(partition by t.MOLIRNSOURCE) R_SI,
                                       --TSO试样         
                                       max(case
                                              when t.HEATNO = t9.HEATNO and t9.xh = t9.cnt then
                                               t9.Mn
                                            end) over(partition by t.HEATNO) Z_MN,
                                        max(case
                                              when t.HEATNO = t9.HEATNO and t9.xh = t9.cnt then
                                               t9.C
                                            end) over(partition by t.HEATNO) Z_C,
                                        max(case
                                              when t.HEATNO = t9.HEATNO and t9.xh = t9.cnt then
                                               t9.P
                                            end) over(partition by t.HEATNO) Z_P,
                                        to_char(t.optdate, 'yyyy-MM-dd hh24:mi') OPTDISP --操作时间
                        from (select *
                                  from stl_bof_optinfo
                                 where 1 = 1
                                 {0}
                                union
                                select *
                                  from J#stl_bof_optinfo
                                 where 1 = 1
                                 {0}
                                ) t
                          left join (select y.*
                                       from (select x.*,
                                                    row_number() over(partition by x.heatno order by x.assaytypecode) xh
                                               from (select *
                                                       from stl_chemelement
                                                      where upper(substr(assaytypecode, 1, 1)) = 'C'
                                                      {1}
                                                     union
                                                     select *
                                                       from J#stl_chemelement
                                                      where upper(substr(ASSAYTYPECODE, 1, 1)) = 'C'
                                                         {1}
                                                    ) x) y) t1 on t.heatno = t1.heatno
                            left join (select y.*
                                       from (select x.*,
                                                    count(1) over(partition by x.HEATNO) cnt,
                                                    row_number() over(partition by x.HEATNO order by x.ASSAYTYPECODE asc, x.assaydate asc) xh,
                                                    substr(x.assaytypecode, 1, 1) gw
                                               from (select *
                                                       from STL_CHEMELEMENT
                                                      where 1 = 1
                                                         and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
                                                         and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                           {1}
                                                     union
                                                     select *
                                                       from J#STL_CHEMELEMENT
                                                      where 1 = 1
                                                        {1}
                                                         and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
                                                         and upper(SUBSTR(CHECKNO, -1)) = 'S') x) y) t9 on t9.heatno = t.heatno
                           left join (select *
                                       from stl_chemelement where 1 = 1 {2}
                                     union
                                     select * from J#stl_chemelement where 1 = 1 {2}) t10 on t10.heatno = t.MOLIRNSOURCE
                          left join (select *
                                       from ppc_steel_heat where 1 = 1 {3}
                                     union
                                     select * from J#ppc_steel_heat where 1 = 1 {3}) t4 on t.heatno = t4.heatno
                         order by OPTDISP";
            sqlStr = lgCommon.stringFormat(sqlStr, sqlConditionList);
            return sqlStr;

        }
        /// <summary>
        /// 返回转炉操作记录报表查询
        /// </summary>
        /// <param name="sqlConditionList"></param>
        /// <returns></returns>
        public static string returnSqlOfBofOperatioReport(ArrayList sqlConditionList)
        {
            string sqlStr = "";
            sqlStr = @"select distinct t.heatno,
                                                t4.aim_grade_code plansteel,
                                                t.stationcode,
                                                t.sage,
                                                t.gage,
                                                t.o2consume,
                                                nvl(t.ironpotwgt, '0') ironpotwgt,
                                                t.wsteelwgt,
                                                t.pigironwgt,
                                                /*case when t6.baleleavewgt is not null then round(((nvl(t.ironpotwgt, '0')+nvl(t.wsteelwgt, '0')+nvl(t.pigironwgt, '0')))*1000/(t6.pfbalewgt - t6.baleleavewgt),2) else 0 end iron_sum,*/
                                                case when t6.baleleavewgt is not null then round(((nvl(t.ironpotwgt, '0')+nvl(t.wsteelwgt, '0')+nvl(t.pigironwgt, '0')))*1000/(decode(t6.pfbalewgt - t6.baleleavewgt,null,1,0,1,t6.pfbalewgt - t6.baleleavewgt)),2) else 0 end iron_sum,
                                                t.brpstime,
                                                t.potno,
                                                t.potdistinction,
                                                t.potwrapstate,
                                                t6.pfbalewgt - t6.baleleavewgt tappingwgt,
                                                decode(substr(t.shiftcode, 1, 1), '1', '早', '2', '中', '3', '晚') bc,
                                                decode(substr(t.shiftcode, 2, 1), '1', '甲', '2', '乙', '3', '丙', '4', '丁') bb,
                                                decode(blkredeffects, '0', '失败', '1', '成功', '2', '未投', '3', '一般') blkredeffects,
                                                t.subage,
                                                t.changestarttime,
                                                t.openoxygentime,
                                                round(t.tappingmaxangle,2) tappingmaxangle,round(t.psmaxangle,2) psmaxangle,
                                                t.irontemperature,
                                                t.wsteelwgt_fg,
                                                t.yakuaiwgt_fg,
                                                t.zhasteelwgt_fg,
                                                t.ironwgt_fg,
                                                t.broken_fg,
                                                t.terminusc,
                                                t.terminuso2,
                                                (select nvl(temperature,0)
                                                  from (select *
                                                          from (SELECT *
                                                                  FROM stl_bof_subsampling 
                                                                 WHERE samplingtype = 'TSC'
                                                                union
                                                                SELECT *
                                                                  FROM j#stl_bof_subsampling 
                                                                 WHERE samplingtype = 'TSC')
                                                         order by samplingdate desc)
                                                 WHERE heatno = t.heatno
                                                   AND rownum = 1) b1temperature,
                                                (select nvl(temperature,0)
                                                  from (select *
                                                          from (SELECT *
                                                                  FROM stl_bof_subsampling 
                                                                 WHERE samplingtype = 'TSO'
                                                                union
                                                                SELECT *
                                                                  FROM j#stl_bof_subsampling 
                                                                 WHERE samplingtype = 'TSO')
                                                         order by samplingdate desc)
                                                  WHERE heatno = t.heatno
                                                    AND rownum = 1) b2temperature,
                                                nvl(t.n2consume, 0) + nvl(t.topn2consume, 0) n2sum,
                                                t.irontemperature,
                                                '0.9' o2press,
                                                nvl(t.b2time, 0) + nvl(t.b3time, 0) + nvl(t.b4time, 0) +
                                                nvl(t.b5time, 0) dctime,
                                                to_char(t.changestarttime, 'HH24:mi') changestarttime,
                                                to_char(t.openoxygentime, 'HH24:mi') openoxygentime,
                                                round(nvl(t.supplyoxygentime / 60, 0), 1) supplyoxygentime,
                                                round(t.supplyoxygentime, 1) supplyoxygentime_s,
                                                t4.plan_lines plan_lines,
                                                t.terminustept tappingtemperature,
                                                t.tappingtime,
                                                to_char(t.tappingstarttime, 'HH24:mi') tappingstarttime,                                                
                                                max(case
                                                      when t.heatno = t2.heatno
                                                           and t2.xh = t2.cnt then
                                                       t2.c
                                                    end) over(partition by t.heatno) f_c,
                                                max(case
                                                      when t.heatno = t2.heatno
                                                           and t2.xh = t2.cnt then
                                                       t2.mn
                                                    end) over(partition by t.heatno) f_mn,
                                                max(case
                                                      when t.heatno = t2.heatno
                                                           and t2.xh = t2.cnt then
                                                       t2.si
                                                    end) over(partition by t.heatno) f_si,
                                                max(case
                                                      when t.heatno = t2.heatno
                                                           and t2.xh = t2.cnt then
                                                       t2.s
                                                    end) over(partition by t.heatno) f_s,
                                                max(case
                                                      when t.heatno = t2.heatno
                                                           and t2.xh = t2.cnt then
                                                       t2.p
                                                    end) over(partition by t.heatno) f_p,
                                                
                                                max(case
                                                      when t.heatno = t2.heatno
                                                           and t2.xh = t2.cnt then
                                                       t2.als
                                                    end) over(partition by t.heatno) f_als,
                                                
                                                max(case
                                                      when t.molirnsource = t10.heatno then
                                                       t10.mn
                                                    end) over(partition by t.molirnsource) r_mn,
                                                max(case
                                                      when t.molirnsource = t10.heatno then
                                                       t10.c
                                                    end) over(partition by t.molirnsource) r_c,
                                                max(case
                                                      when t.molirnsource = t10.heatno then
                                                       t10.p
                                                    end) over(partition by t.molirnsource) r_p,
                                                max(case
                                                      when t.molirnsource = t10.heatno then
                                                       t10.s
                                                    end) over(partition by t.molirnsource) r_s,
                                                max(case
                                                      when t.molirnsource = t10.heatno then
                                                       t10.si
                                                    end) over(partition by t.molirnsource) r_si,
                                                
                                                max(case
                                                      when t.heatno = t9.heatno
                                                           and t9.xh = t9.cnt then
                                                       t9.mn
                                                    end) over(partition by t.heatno) z_mn,
                                                max(case
                                                      when t.heatno = t9.heatno
                                                           and t9.xh = t9.cnt then
                                                       t9.c
                                                    end) over(partition by t.heatno) z_c,
                                                max(case
                                                      when t.heatno = t9.heatno
                                                           and t9.xh = t9.cnt then
                                                       t9.p
                                                    end) over(partition by t.heatno) z_p,
                                                max(case
                                                      when t.heatno = t9.heatno
                                                           and t9.xh = t9.cnt then
                                                       t9.s
                                                    end) over(partition by t.heatno) z_s,
                                                
                                                max(case
                                                      when t.heatno = t9.heatno
                                                           and t9.xh = t9.cnt then
                                                       t9.si
                                                    end) over(partition by t.heatno) z_si,
                                                case when max(case
                                                  when t.molirnsource = t10.heatno then
                                                   t10.mn
                                                end) over(partition by t.molirnsource) is not null then
                                                round(max(case
                                                  when t.heatno = t9.heatno and t9.xh = t9.cnt then
                                                   t9.mn
                                                end) over(partition by t.heatno)/max(case
                                                  when t.molirnsource = t10.heatno then
                                                   decode(t10.mn,null,1,0,1)   --t10.mn 铁水mn元素有可能为0
                                                end) over(partition by t.molirnsource),2) else 0 end mn_harvest_rate,
                                                t4.cast_id ccmno,
                                                (t4.cast_id) || '-' || (t4.cast_seq) ccmno,
                                                t.memo,
                                                to_char(t.optdate, 'yyyy-MM-dd hh24:mi') optdisp,
                                                t.remark2
                                from   (select *
                                        from   stl_bof_optinfo
                                        where  1 = 1
                                        {0}
                                        union all
                                        select *
                                        from   j#stl_bof_optinfo
                                        where  1 = 1
                                     {0}) t
                                left   join (select y.*
                                             from   (select x.*,
                                                            
                                                            row_number() over(partition by x.heatno order by x.assaytypecode) xh
                                                     from   (select *
                                                             from   stl_chemelement
                                                             where  upper(substr(assaytypecode, 1, 1)) = 'C'
                                                            {1}
                                                             union all
                                                             select *
                                                             from   j#stl_chemelement
                                                             where  upper(substr(assaytypecode, 1, 1)) = 'C'
                                                             {1}) x) y) t1 on t.heatno =
                                                                                                                                  t1.heatno
                                left   join (select y.*
                                             from   (select x.*,
                                                            count(1) over(partition by x.heatno) cnt,
                                                            row_number() over(partition by x.heatno order by x.assaytypecode desc, x.assaydate desc) xh,
                                                            substr(x.assaytypecode, 1, 1) gw
                                                     from   (select *
                                                             from   stl_chemelement
                                                             where  1 = 1
                                                             {1}
                                                             and    upper(substr(checkno, -1)) = 'S'
                                                             and    upper(substr(checkno, 12, 2)) = 'GP'
                                                             union all
                                                             select *
                                                             from   j#stl_chemelement
                                                             where  1 = 1
                                                             {1}
                                                             and    upper(substr(checkno, -1)) = 'S'
                                                             and    upper(substr(checkno, 12, 2)) = 'GP') x) y) t2 on t2.heatno =
                                                                                                                      t.heatno

                                left   join (select y.*
                                             from   (select x.*,
                                                            count(1) over(partition by x.heatno) cnt,
                                                            row_number() over(partition by x.heatno order by x.assaytypecode desc, x.assaydate desc) xh,
                                                            substr(x.assaytypecode, 1, 1) gw
                                                     from   (select *
                                                             from   stl_chemelement
                                                             where  1 = 1
                                                             {1}
                                                             and    upper(substr(checkno, 12, 1)) = 'C'
                                                             and    upper(substr(checkno, -1)) = 'I'
                                                             and    upper(substr(checkno, 13, 1)) <> 'P'
                                                             union all
                                                             select *
                                                             from   j#stl_chemelement
                                                             where  1 = 1
                                                             {1}
                                                             and    upper(substr(checkno, 12, 1)) = 'C'
                                                             and    upper(substr(checkno, -1)) = 'I'
                                                             and    upper(substr(checkno, 13, 1)) <> 'P') x) y) t3 on t3.heatno =
                                                                                                                      t.heatno
                                left   join (select y.*
                                             from   (select x.*,
                                                            count(1) over(partition by x.heatno) cnt,
                                                            row_number() over(partition by x.heatno order by x.assaytypecode) xh,
                                                            substr(x.assaytypecode, 1, 1) gw
                                                     from   (select *
                                                             from   stl_chemelement
                                                            where  1 = 1
                                                             {1}
                                                             and    upper(substr(checkno, 12, 1)) = 'C'
                                                             and    upper(substr(checkno, -1)) = 'S'
                                                             union all
                                                             select *
                                                             from   j#stl_chemelement
                                                             where  1 = 1
                                                             {1}
                                                             and    upper(substr(checkno, 12, 1)) = 'C'
                                                             and    upper(substr(checkno, -1)) = 'S') x) y) t9 on t9.heatno =
                                                                                                                  t.heatno
                                left   join (select *
                                             from   stl_chemelement where 1 = 1 {1}
                                             union all
                                             select * from j#stl_chemelement where 1 = 1 {1}) t10 on t10.heatno =
                                                                                     t.molirnsource
                                left   join (select *
                                             from   ppc_steel_heat where 1 = 1 {3}
                                             union all
                                             select * from j#ppc_steel_heat where 1 = 1 {3}) t4 on t.heatno =
                                                                                   t4.heatno

                                /*left   join (select *
                                             from   ppc_steel_heat  where 1 = 1 {3}
                                             union all
                                             select * from j#ppc_steel_heat where 1 = 1 {3}) t5 on t.heatno =
                                                                                   t5.heatno*/
                                left   join (select heatno,
                                                    pfbalewgt,
                                                    baleleavewgt,
                                                    isrs,
                                                    mwrapno,
                                                    billetfixsize
                                             from   stl_ccm_optinfo
                                              where  1 = 1
                                                             {2}
                                             union all
                                             select heatno,
                                                    pfbalewgt,
                                                    baleleavewgt,
                                                    isrs,
                                                    mwrapno,
                                                    billetfixsize
                                             from   j#stl_ccm_optinfo    
                                            where  1 = 1
                                              {2}) t6 on t.heatno = t6.heatno
                                order  by optdisp
                                ";
            sqlStr = lgCommon.stringFormat(sqlStr, sqlConditionList);
            return sqlStr;
        }
        /// <summary>
        /// 返回转炉出钢c报表查询sql
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        public static string returnSqlOfBofTapping(ArrayList sqlConditionList)
        {
                                    string sqlStr = "";
                                    sqlStr = @" select distinct t.HEATNO, --炉号
                                        t4.aim_grade_code PLANSTEEL,  --钢种
                                        case substr(t.SHIFTCODE, 2, 1)--班组
                                            when '1' then
                                             '甲'
                                            when '2' then
                                             '乙'
                                            when '3' then
                                             '丙'
                                            when '4' then
                                             '丁'
                                         end BB,
                                        case substr(t.SHIFTCODE, 1, 1)--班次
                                           when '1' then
                                            '早'
                                           when '2' then
                                            '中'
                                           when '3' then
                                            '夜'
                                         end BC,
                                        nvl(t.B2TIME,0) B2TIME, --2#吹时间(s)
                                        nvl(t.B3TIME,0) B3TIME, --3#吹时间(s)
                                        nvl(t.B4TIME,0) B4TIME, --4#吹时间(s)
                                        nvl(t.B5TIME,0) B5TIME, --5#吹时间(s)
                                        --LD2(第二次取样)        
                                        max(case
                                              when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
                                               t3.Mn
                                            end) over(partition by t.HEATNO) Z_MN,
                                        max(case
                                              when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
                                               t3.C
                                            end) over(partition by t.HEATNO) Z_C,
                                        max(case
                                              when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
                                               t3.P
                                            end) over(partition by t.HEATNO) Z_P,
                                        max(case
                                              when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
                                               t3.S
                                            end) over(partition by t.HEATNO) Z_S,
                                        
                                        to_char(t.optdate, 'yyyy-MM-dd hh24:mi') OPTDISP --操作时间
                          from (select *
                                  from STL_bof_optinfo
                                 where 1 = 1
                                 {0}
                                union
                                select *
                                  from J#STL_bof_optinfo
                                 where 1 = 1
                                 {0}
                                ) t
                          left join (select y.*
                                       from (select x.*,
                                                    count(1) over(partition by x.HEATNO) cnt,
                                                    row_number() over(partition by x.HEATNO order by x.ASSAYTYPECODE asc, x.assaydate asc) xh,
                                                    substr(x.assaytypecode, 1, 1) gw
                                               from (select *
                                                       from STL_CHEMELEMENT
                                                      where 1 = 1
                                                         and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
                                                         and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                       {1}
                                                     union
                                                     select *
                                                       from J#STL_CHEMELEMENT
                                                      where  1 = 1 {1}
                                                         and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
                                                         and upper(SUBSTR(CHECKNO, -1)) = 'S') x) y  ) t3 on t.heatno = t3.heatno
                          left join (select *
                                       from ppc_steel_heat where  1 = 1 {2}
                                     union
                                     select * from J#ppc_steel_heat  where 1 = 1 {2}) t4 on t.heatno = t4.heatno
                         order by OPTDISP";
                                    sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr,sqlConditionList);
                                    return sqlStr;
        }

        /// <summary>
        /// 终点MN考核报表
        /// </summary>
        /// <param name="sqlConditonList"></param>
        /// <returns></returns>
        public static string ReturnSqlOfBofChecnNmReport(ArrayList sqlConditonList)
        {
                                    string sqlStr = "";
                                    sqlStr = @"select distinct t.HEATNO,--炉号
                               t.STATIONCODE,             --岗位编号
                               substr(t.SHIFTCODE, 2, 1) SHIFTCODE,     --班组(甲、乙、丙、丁)
                               max(case
                                    when t.HEATNO = t1.HEATNO and t1.xh = t1.cnt then
                                     t1.Mn
                                  end) over(partition by t.HEATNO) Z_MN,
                               t2.stdmin, --最小值(内控)
                               t2.stdmax, --最大值(内控)
                               to_char(t.optdate, 'yyyy-MM-dd') OPTDISP --日期
                                  
                             from (select * from stl_bof_optinfo where 1 = 1
                                      {0}
                                     union all  select * from j#stl_bof_optinfo where 1 = 1
                                     {0}
                                   ) t
                               left join (select y.*
                                       from (select x.*,
                                                    count(1) over(partition by x.HEATNO) cnt,
                                                    row_number() over(partition by x.HEATNO order by x.ASSAYTYPECODE asc, x.assaydate asc) xh,
                                                    substr(x.assaytypecode, 1, 1) gw
                                               from (select *
                                                       from STL_CHEMELEMENT
                                                      where 1 = 1 {1} 
                                                        and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
                                                        and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                     union all
                                                     select *
                                                       from J#STL_CHEMELEMENT
                                                      where 1 = 1 {1} 
                                                        and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
                                                        and upper(SUBSTR(CHECKNO, -1)) = 'S') x) y) t1 on t1.heatno = t.heatno  
                                left join ( select distinct t3.gycode,t4.itemcode,t4.stdmin,t4.stdmax  --decode(t1.itemcode,'Mn',t1.stdmin,'') MN_Min, decode(t1.itemcode,'Mn',t1.stdmax,'') MN_Max
                                                    from scm_sortcode_chem t3, scm_standard_chem t4
                                                   where t3.cic = t4.cic
                                                     and t3.stdstyle = '1' 
                                                     and t4.itemcode= 'Mn'  {2}) t2 on t2.gycode = t.heatno 
                                  order by  substr(OPTDISP,9,2)  ";
                                    sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr,sqlConditonList);
                                    return sqlStr;
 
        }

        /// <summary>
        /// 钢铁物料消耗报表
        /// </summary>
        /// <returns></returns>
        public static string ReturnSqlOfSteelAdditStats(ArrayList sqlConditionList)
        {
                        string sqlStr = "";
                        sqlStr = @"select A.HEATNO,                     --炉号 
                               A.station STATIONCODE,                --岗位编码
                               nvl(round(B.CPL, 3), 0) CPL,  --合格铸坯量（T）
                               case
                                 when (nvl(A.PFBALEWGT, 0) - nvl(A.BALELEAVEWGT, 0)) > 0 then
                                  round((nvl(A.PFBALEWGT, 0) - nvl(A.BALELEAVEWGT, 0)), 3)
                                 else
                                  0
                               end GSL,   --GSL(合格钢水量) = (上台大包重量-下台大包重量)
                               (select substr(mes_lg_common.getlgshift1(A.RecFlag), 2, 1) from dual) SHIFTCODE,--班组
                               case
                                 when to_char(A.RecFlag, 'yyyy-MM-dd HH24:mi') between '{2}' and '{3}'  then
                                  '1'
                                 else
                                  '0'
                               end BECURR --1#吹开始时刻(操作时间)
                          from (select *
                                  from (select t.*,
                                               row_number() over(partition by HEATNO order by RecFlag) XH
                                          from (select t.recflag,station, t2.*
                                                  from (select t.heatno,
                                                               case
                                                                 when B1STARTTIME is not null then
                                                                  B1STARTTIME
                                                                 else
                                                                  OPTDATE
                                                               end RecFlag,t.stationcode station
                                                          from STL_bof_optinfo t
                                                         where  1 = 1 {0}
                                                        union all
                                                        select t.heatno,
                                                               case
                                                                 when B1STARTTIME is not null then
                                                                  B1STARTTIME
                                                                 else
                                                                  OPTDATE
                                                               end RecFlag,t.stationcode station
                                                          from J#STL_bof_optinfo t
                                                         where 1 = 1 {0}) t,
                                                       (select *
                                                          from STL_ccm_optinfo where  1 = 1 {1}
                                                        union
                                                        select * from J#STL_Ccm_Optinfo where 1 = 1 {1}) t2
                                                 where t.heatno = t2.heatno) t)
                                 where XH = 1) A,
                               (select distinct substr(CHARGE_NO, 1, 10) HEATNO,
                                                sum(nvl(SLAB_WGT, 0)) over(partition by substr(CHARGE_NO, 1, 10)) CPL
                                  from (select CHARGE_NO, (SLAB_WGT/1000) SLAB_WGT --合格铸坯量(T)
                                          from stl_incision_view
                                         where 1 = 1 and to_char(to_date(substr(SLAB_CUT_DTIME,1,8),'yyyy-MM-dd  hh24:mi:ss'),'yyyy-MM-dd') between '{4}' and '{5}'
                                       ) t) B
                         where A.HEATNO = B.HEATNO(+)  ";

                        sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr,sqlConditionList);
                        return sqlStr;
        }


        public static string ReturnSqlOfBofQueryInfo(ArrayList sqlConditionList)
        {
            string sqlStr = "";
            sqlStr = @"select distinct b.HEATNO,                  --炉号    
                           b.STATIONCODE,                      --岗位编号
                           substr(b.SHIFTCODE, 2, 1) SHIFTCODE,--班组(甲、乙、丙、丁)
                           nvl(b.IRONPOTWGT, 0) TSL,           --装入铁水量
                           nvl(b.PIGIRONWGT, 0) STL,           --装入生铁量（T）
                           nvl(b.WSTEELWGT, 0) FGL,            --装入废钢量（T）
                           nvl(b.RSWGT, 0) HLL,                --回炉重量（T）
                           nvl(h.jmwgt,0) HJL,                 --加入合金量(Kg)        
                           to_char(b.CHANGESTARTTIME,'yyyy-mm-dd hh24:mi:ss') starttime, --兑铁开始时刻
                           to_char(b.TAPPINGENDTIME,'yyyy-mm-dd hh24:mi:ss') endtime,    --出钢结束时刻
                           nvl((b.TAPPINGENDTIME-b.CHANGESTARTTIME )*24*60,0) time,      --作业时间(min)  
                          case
                             when to_char(RecFlag, 'yyyy-MM-dd HH24:mi') between '{0}' and '{1}' then
                              '1'
                             else
                              '0'
                           end BECURR                        --1#吹开始时刻(操作时间)
                      from (select *
                              from (select t.*,
                                     row_number() over(partition by t.HEATNO order by t.RecFlag) XH
                                from (select t.*,
                                             case
                                               when B1STARTTIME is not null then
                                                B1STARTTIME
                                               else
                                                OPTDATE
                                             end RecFlag
                                        from STL_BOF_OPTINFO t
                                       where  1 = 1
                                      and  optdate >= trunc(to_date('{2}','yyyy-mm-dd'))
                                      and optdate  < trunc(to_date('{3}','yyyy-mm-dd')) + 1
                                      
                                      union all
                                      select t.*,
                                             case
                                               when B1STARTTIME is not null then
                                                B1STARTTIME
                                               else
                                                OPTDATE
                                             end RecFlag
                                        from J#STL_BOF_OPTINFO t
                                       where 1 = 1
                                      and  optdate >= trunc(to_date('{2}','yyyy-mm-dd'))
                                      and optdate  < trunc(to_date('{3}','yyyy-mm-dd')) + 1 ) t) v where XH = 1) b, 
                              (select distinct s.heatno,
                                              sum(s.jmwgt) over(partition by s.heatno order by s.heatno) jmwgt,
                                              s.materieltype,
                                              substr(s.stationcode, 2, 2) code
                                from stl_additives s
                               where s.materieltype = 'HJ'
                                 and substr(s.stationcode, 1, 1) = 'C'
                                 and jmdate >= trunc(to_date('{2}','yyyy-mm-dd'))
                                  and jmdate < trunc(to_date('{3}','yyyy-mm-dd')) + 1
                              union
                              select distinct s.heatno,
                                              sum(s.jmwgt) over(partition by s.heatno order by s.heatno) jmwgt,
                                              s.materieltype,
                                              substr(s.stationcode, 2, 2) code
                                from j#stl_additives s
                               where s.materieltype = 'HJ'
                                 and jmdate >= trunc(to_date('{2}','yyyy-mm-dd'))
                                  and jmdate < trunc(to_date('{3}','yyyy-mm-dd')) + 1
                                 and substr(s.stationcode, 1, 1) = 'C') h 
                         where b.heatno = h.heatno(+)  order by b.heatno";
            sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr, sqlConditionList);
            return sqlStr;

        }
    }
}