XinYu_LG2MES/ClientSolution/Core.Mes.Client.Common/lgSqlCollection/SqlCollectionBof.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreFS.CA06;
using System.Data;
using System.Collections;

namespace Core.Mes.Client.Common
{
    /// <summary>
    /// 存储bof报表相关sql
    /// </summary>
   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,
                                                t.brpstime,
                                                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.ironpotwgt,
                                                t.wsteelwgt,
                                                t.pigironwgt,
                                                t.changestarttime,
                                                t.openoxygentime,
                                                t.irontemperature,
                                                t.terminusc,
                                                t.terminuso2,
                                                t.b1temperature,
                                                t.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,
                                                t4.cast_id ccmno,
                                                (t4.cast_id) || '-' || (t4.cast_seq) ccmno,
                                                t.memo,
                                                to_char(t.optdate, 'yyyy-MM-dd hh24:mi') 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.*,
                                                            
                                                            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;
       }
    }
}