XinYu_LG2MES/ClientSolution/Core.Mes.Client.Common/lgSqlCollection/SqlCollection.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
{
    public class SqlCollection
    {
        /// <summary>
        /// 返回2钢基础数据sql
        /// </summary>
        /// <returns></returns>
        public static string returnScm_Base_InfoByAllCondition(string sqlCondition)
        {
            string sqlStr = "select * from scm_base_info a where 1 = 1 {0}";
            sqlStr = string.Format(sqlStr, sqlCondition);
            return sqlStr;
        }


        public static string returnOfTbg02_Charge_Comm(string sqlCondition)
        {
            string sqlStr = "select * from tbg02_charge_comm@xg3q a where 1 = 1 {0}";
            sqlStr = string.Format(sqlStr, sqlCondition);
            return sqlStr;
        }

        public class LgResMgtSqlCollection
        {
            public static string returnStl_Mis_Importiron(string sqlConditon)
            {
                string sqlstr = "";
                sqlstr = string.Format("select * from stl_mis_importiron where 1 = 1 {0}", sqlConditon);
                return sqlstr;
            }

            public static string returnSqlForStl_Hmp_Tempsampling(string sqlConditon)
            {
                string sqlstr = "";
                sqlstr = string.Format("select * from stl_hmp_tempsampling where 1 = 1 {0}", sqlConditon);
                return sqlstr;
            }
        }
      
        /// <summary>
        /// Ccm 操作集合
        /// </summary>
        public class CcmSqlCollection
        {

          

            /// <summary>
            /// 连铸主控机操作报表
            /// </summary>
            /// <param name="sqlCondtion"></param>
            /// <returns></returns>
            public static string returnSqlOfCcmOption(string sqlCondtion)
            {
                string sqlstr = "";
                sqlstr = @"select a.heatno,
                           /*a.mouldno,*/
                           a.mouldnumid mouldno,
                           a.plansteel,
                           v.plan_lines,
                           a.billetsection,
                           a.billetsection2,
                           case a.baleflowflag
                             when 0 then
                              '否'
                             when 1 then
                              '是'
                           end baleflowflag,
                           a.mouldstovenum,
                           a.pfbaletempt,
                           to_char(a.balepftime, 'HH24:mi') balepftime,
                           to_char(a.balestarttime, 'HH24:mi') balestarttime,
                           to_char(a.baleendtime, 'HH24:mi') baleendtime,
                           round((a.baleendtime - a.balestarttime) * 24 * 60) 周期,
                           a.pfbalewgt,
                           a.baleleavewgt,
                           a.stationcode, a.mwrapno, a.lmltempt, 
                           a.crystallizerid,a.crystallizerid2,a.crystallizerid3,a.crystallizerid4, 
                           a.crystallizertimes6, a.optpersonnel,  
                           a.memo,
                           a.watermeterno,
                           a.coveringslag,
                           a.arpre_s1,
                           a.arpre_s2,
                           a.upwaterpre_s1,
                           a.upwaterpre_s2,
                           a.waterchage_s1,
                           a.waterchage_s2,
                           a.liqfluct_s1,
                           a.liqfluct_s2,
                           a.closesteel,
                           a.steelstick_s1,
                           a.steelstick_s2,
                           a.biasflow_s1,
                           a.biasflow_s2,
                           a.baleisdown,
                           a.rhythmfast,
                           a.tempt_high,
                           a.tempt_low,
                           a.notice_s1,
                           a.notice_s2,
                           a.jjqslkn_s1,
                           a.jjqslkw_s1,
                           a.jjqslzz_s1,
                           a.jjqslzy_s1,
                           a.jjqswckn_s1,
                           a.jjqswckw_s1,
                           a.jjqswczz_s1,
                           a.jjqswczy_s1,
                           a.jjqslkn_s2,
                           a.jjqslkw_s2,
                           a.jjqslzz_s2,
                           a.jjqslzy_s2,
                           a.jjqswckn_s2,
                           a.jjqswckw_s2,
                           a.jjqswczz_s2,
                           a.jjqswczy_s2,
                           a.billetcondition,
                           a.midwgt_min,
                           a.width_s1,
                           a.width_s2,a.isaddtop,a.tempt_max,a.tempt_min,a.lasu_max,a.lasu_min,a.lasu2_max,a.lasu2_min,
                           a.S1_JJQ_WGT,a.S2_JJQ_WGT,a.s1_ld_wgt,a.s2_ld_wgt,a.s1_czld_wgt,a.s1_wqld_wgt,
                           nvl(a.pfbalewgt, 0) - nvl(a.baleleavewgt, 0) jgwgt,
                           to_char(a.baleleavetime, 'HH24:mi') baleleavetime,
                           /*v.cast_id || '-' || v.cast_seq cast_id,*/
                           a.heat_seq cast_id,
                           a.s1billetlength len_1,
                           a.s2billetlength len_2,
                           a.crystallizertimes crystallizertimes1,
                           a.crystallizertimes2,
                           a.memo,
                           a.shiftcode,
                           to_char(a.optdate, 'yyyy-MM-dd') optdisp,
                           a.tg_fac,
                           a.sk_fac,
                           a.potno，
                           a.JCDJ,
                           a.MWRAPTNO,
                           a.S1_ALERT,
                           a.S2_ALERT,
                           a.RESIDUETHICKNESS,
                           ROUND(NVL((SELECT SUM(TA.SLAB_WGT/1000) FROM TBG02_SLAB_COMM@XG3Q TA WHERE TA.SLAB_NO LIKE SUBSTR(A.HEATNO,1,9)||'%' AND (TA.MATRL_END_CAUSE_CD IS NULL OR TA.MATRL_END_CAUSE_CD NOT IN ('4','5'))),0),3) RKL,
                           ROUND(NVL((SELECT SUM(TA.SLAB_WGT/1000) FROM TBG02_SLAB_COMM@XG3Q TA WHERE TA.SLAB_NO LIKE SUBSTR(A.HEATNO,1,9)||'%' AND (TA.MATRL_END_CAUSE_CD IS NULL OR TA.MATRL_END_CAUSE_CD NOT IN ('4','5'))),0),3) - (NVL(A.PFBALEWGT, 0) - NVL(A.BALELEAVEWGT, 0)) PCL,
                           /*TO_CHAR((NVL((SELECT SUM(TA.SLAB_WGT/1000) FROM TBG02_SLAB_COMM@XG3Q TA WHERE TA.SLAB_NO LIKE SUBSTR(A.HEATNO,1,9)||'%' AND (TA.MATRL_END_CAUSE_CD IS NULL OR TA.MATRL_END_CAUSE_CD NOT IN ('4','5'))),0)/NVL(A.PFBALEWGT - A.BALELEAVEWGT, 9999)-1)*100,'fm9999990.00')||'%' PCBL,*/
                           TO_CHAR((NVL((SELECT SUM(TA.SLAB_WGT/1000) FROM TBG02_SLAB_COMM@XG3Q TA WHERE TA.SLAB_NO LIKE SUBSTR(A.HEATNO,1,9)||'%' AND (TA.MATRL_END_CAUSE_CD IS NULL OR TA.MATRL_END_CAUSE_CD NOT IN ('4','5'))),0)/decode(A.PFBALEWGT - A.BALELEAVEWGT,null,9999,0,9999,A.PFBALEWGT - A.BALELEAVEWGT)-1)*100,'fm9999990.00')||'%' PCBL,
                           SUM(ROUND(NVL((SELECT SUM(TA.SLAB_WGT/1000) FROM TBG02_SLAB_COMM@XG3Q TA WHERE TA.SLAB_NO LIKE SUBSTR(A.HEATNO,1,9)||'%' AND (TA.MATRL_END_CAUSE_CD IS NULL OR TA.MATRL_END_CAUSE_CD NOT IN ('4','5'))),0),3) - (NVL(A.PFBALEWGT, 0) - NVL(A.BALELEAVEWGT, 0))) 
                                                 OVER (PARTITION BY A.MOULDNUMID ORDER BY A.HEAT_SEQ) LJPCL,
                            round(a.tempt_max - a.LMLTEMPT) overheat
                    from   (select *
                            from   stl_ccm_optinfo
                            where  1 = 1
                            {0}
                            union
                            select *
                            from   j#stl_ccm_optinfo
                            where  1 = 1
                             {0}) a,
                           
                           stl_ccm_mouldinfo u,
                           (select *
                            from   ppc_steel_heat
                            union
                            select * from j#ppc_steel_heat) v
                     --,(select heatno,min(SAMPLINGVALUE) min_temp,max(SAMPLINGVALUE) max_temp from (select HEATNO ,SAMPLINGVALUE from stl_tempsampling  tt
                       -- where   DISPOSALTIME = '01' and substr(stationcode, 1, 1)='G' and samplingdate > sysdate-365
                  --union select HEATNO ,SAMPLINGVALUE from J#stl_tempsampling aa
                        --where  DISPOSALTIME = '01'  and substr(stationcode, 1, 1)='G' and samplingdate > sysdate-365  )group by heatno) t 
                    where  a.mouldno = u.mouldno(+)
                    and    a.heatno = v.heatno(+)
                    --and a.heatno = t.heatno(+)
                    order  by a.optdate";
                sqlstr = string.Format(sqlstr,sqlCondtion);
                return sqlstr;
            }

          /// <summary>
          /// 返回炼钢生产报表sql
          /// </summary>
          /// <param name="sqlConditionList"></param>
          /// <returns></returns>
            public static string ReturnSqlOfLgProdueceReport(ArrayList sqlConditionList)
            {
                                                          string sqlStr = "";
                                                        sqlStr = @"select distinct t.heatno,
                                                        t.stationcode stationcode,
                                                        to_char(t.optdate, 'YYYY-MM-DD') optdate,
                                                        t.shiftcode,
                                                        t.downgashoodtime||'' downgashoodtime,
                                                        substr(t.shiftcode, 1, 1) bc,
                                                        substr(t.shiftcode, 2, 1) bb,
                                                        t4.aim_grade_code plansteel,
                                                        case
                                                          when substr(t4.fact_route, 9, 2) != 'E0' then
                                                           '是'
                                                          else
                                                           '否'
                                                        end fact_route,
                                                        '' mnfc,
                                                        nvl((select d.stdmin
                                                            from   scm_standard_chem_view d,
                                                                   (select *
                                                                    from   scm_base_info d
                                                                    where  d.sortcode = '1006'
                                                                    order  by d.basecode) h
                                                            where  d.itemcode = h.basename
                                                            and    steel = t4.aim_grade_code
                                                            and    stdstyle = '1'
                                                            and    d.itemcode = 'MN'
                                                            group  by steel,
                                                                      stdstyle,
                                                                      itemcode,
                                                                      std,
                                                                      stdmin,
                                                                      stdmax,
                                                                      basename,
                                                                      basecode), 0) nkmn,
                                                        case
                                                           when substr(t4.fact_route, 9, 2) != 'E0' then
                                                           
                                                            nvl(max(case
                                                           when t.heatno = t10.heatno
                                                                and t10.xh = t10.cnt then
                                                            t10.mn
                                                         end) over(partition by t.heatno), 0) --精炼
                                                         else nvl(max(case
                                                          when t.heatno = t11.heatno
                                                               and t11.xh = t11.cnt then
                                                           t11.mn
                                                        end) over(partition by t.heatno), 0) --连铸                
                                                         end lforccm,                
                                                        t.sage,
                                                        t.gage,
                                                        t.oggnumid,
                                                        t.emolstltime,
                                                        nvl(t.ironpotwgt, '0') ironpotwgt,
                                                        t.wsteelwgt,
                                                        t.pigironwgt,
                                                        t.joinmarlwgt,
                                                        t.rsteelwgt,
                                                        t.rswgt,
                                                        t.ironpotid,
                                                        t.n2consume,
                                                        t.arconsume,
                                                        t.potdistinction,
                                                        t.potwrapstate,
                                                        nvl(t.n2consume, 0) + nvl(t.topn2consume, 0) n2sum,
                                                        decode(substr(t.molirntype, 1, 1), '1', '半钢', decode(substr(t.molirntype, 1, 1), '2', '脱硫', decode(substr(t.molirntype, 1, 1), '3', '混铁炉', decode(substr(t.molirntype, 1, 1), '4', '直兑', '')))) molirntype,
                                                        t.irontemperature,
                                                        (select cast_id || '-' || cast_seq
                                                         from   ppc_steel_heat
                                                         where  heatno = t.heatno) mouldumid,
                                                        to_char(t.changestarttime, 'HH24:MI') changestarttime,
                                                        to_char(t.openoxygentime, 'HH24:MI') openoxygentime,
                                                        floor(nvl(t.supplyoxygentime / 60, 0)) supplyoxygentime,
                                                        t.supplyoxygentime supplyoxygentime_s,
                                                        floor(nvl(t.b1time / 60, 0)) b1time_m,
                                                        t.b1time b1time_s,
                                                        t.o2consume,
                                                        t.terminustept tappingtemperature,
                                                        t.potno,
                                                        t.tappingtime,
                                                        to_char(t.tappingendtime, 'HH24:MI') tappingendtime,
                                                        to_char(t.tappingstarttime, 'HH24:MI') tappingstarttime,
                                                        t.molstltemperature,
                                                        t.ccmplantempt,
                                                        to_char(t.ccmplantempttime, 'HH24:MI') ccmplantempttime,
                                                        round((t.psendtime - t.changestarttime) * 24 * 60) cycle,
                                                        decode(substr(t4.plan_route, instr(t4.plan_route, 'E') + 1, 1), '0', '', substr(t4.plan_route, instr(t4.plan_route, 'E') + 1, 1)) lfsno,
                                                        (t4.width || '*' || t4.thick) rolling,
                                                        t4.cast_id ccmno,
                                                        t5.si i_si,
                                                        t5.mn i_mn,
                                                        t5.p i_p,
                                                        t5.s i_s,
                                                        t5.c i_as,
                                                        t.blowo2times downheattime,
                                                        t6.pfbalewgt grosswgt,
                                                        t6.baleleavewgt kongwgt,
                                                        t6.pfbalewgt - t6.baleleavewgt tappingwgt,
                                                        t7.arftempt casbeforetemperature,
                                                        t7.arbtempt casaftertemperature,
                                                        t.optdate optdisp,
                                                        t.gasrecoverytime,
                                                        decode(t8.isrs, '1', '精炼回炉', decode(t6.isrs, '1', '连铸回炉', '')) isrs,
                                                        decode(t8.isrs, '1', decode(t6.isrs, '1', (select sum(weightofcvt)
                                                                         from   (select weightofcvt,
                                                                                        smeltingidofcvt
                                                                                 from   stl_es_taphole
                                                                                 union
                                                                                 select weightofcvt,
                                                                                        smeltingidofcvt
                                                                                 from   stl_mis_taphole)
                                                                         where  smeltingidofcvt =
                                                                                t.heatno), ''), '') isrswgt,
                                                        nvl(t.ironpotwgt, '0') + nvl(t.wsteelwgt, '0') +
                                                        nvl(t.pigironwgt, '0') + nvl(t.joinmarlwgt, '0') +
                                                        nvl(t.rsteelwgt, '0') + nvl(t.rswgt, '0') joinstovewgt,
                                                        decode((nvl(t.ironpotwgt, '0') + nvl(t.wsteelwgt, '0') +
                                                               nvl(t.pigironwgt, '0') +
                                                               nvl(t.joinmarlwgt, '0') +
                                                               nvl(t.rsteelwgt, '0') + nvl(t.rswgt, '0')), 0, 0, round((t6.pfbalewgt -
                                                                      t6.baleleavewgt) /
                                                                      (nvl(t.ironpotwgt, '0') +
                                                                      nvl(t.wsteelwgt, '0') +
                                                                      nvl(t.pigironwgt, '0') +
                                                                      nvl(t.joinmarlwgt, '0') +
                                                                      nvl(t.rsteelwgt, '0') +
                                                                      nvl(t.rswgt, '0')) * 100, 2)) steelout,
                                                        t8.ladlecoverage,
                                                        t6.mwrapno,
                                                        t8.eletricityconsume,
                                                        t7.arftempt,
                                                        round(((t8.reposebegintime - t8.arrivetime) * 24 * 60), 0) awaittime,
                                                        t8.refinetime,
                                                        t8.flexibleblowtime,
                                                        t8.arrivetempt,
                                                        t8.leavetempt,
                                                        t7.blowartime,
                                                        t6.billetfixsize,
                                                        t9.billetnum,
                                                        t9.billetwgt
                                        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 *
                                                     from   ppc_steel_heat where 1 = 1 {1}
                                                     union all
                                                     select * from j#ppc_steel_heat where 1 = 1 {1}) t4 on t.heatno =
                                                                                           t4.heatno
                                        left   join (select *
                                                     from   stl_chemelement  where 1 = 1 {2}
                                                     union all
                                                     select * from j#stl_chemelement where 1 = 1 {2}) t5 on t5.heatno =
                                                                                            t.molirnsource
                                        left   join (select heatno,
                                                            pfbalewgt,
                                                            baleleavewgt,
                                                            isrs,
                                                            mwrapno,
                                                            billetfixsize
                                                     from   stl_ccm_optinfo  where 1 = 1 {3}
                                                     union all
                                                     select heatno,
                                                            pfbalewgt,
                                                            baleleavewgt,
                                                            isrs,
                                                            mwrapno,
                                                            billetfixsize
                                                     from   j#stl_ccm_optinfo where 1 = 1 {3} ) t6 on t.heatno = t6.heatno
                                        left   join (select heatno,
                                                            arftempt,
                                                            arbtempt,
                                                            blowartime
                                                     from   stl_cas_optinfo  where 1 = 1 {3}
                                                     union all
                                                     select heatno,
                                                            arftempt,
                                                            arbtempt,
                                                            blowartime 
                                                     from   j#stl_cas_optinfo where 1 = 1 {3}) t7 on t.heatno = t7.heatno
                                        left   join (select heatno,
                                                            max(isrs) isrs,
                                                            max(eletricityconsume) eletricityconsume,
                                                            max(ladlecoverage) ladlecoverage,
                                                            max(reposebegintime) reposebegintime,
                                                            max(arrivetime) arrivetime,
                                                            max(refinetime) refinetime,
                                                            max(flexibleblowtime) flexibleblowtime,
                                                            max(arrivetempt) arrivetempt,
                                                            max(leavetempt) leavetempt
                                                     from   (select heatno,
                                                                    isrs,
                                                                    eletricityconsume,
                                                                    ladlecoverage,
                                                                    reposebegintime,
                                                                    arrivetime,
                                                                    refinetime,
                                                                    flexibleblowtime,
                                                                    arrivetempt,
                                                                    leavetempt
                                                             from   stl_lfs_optinfo where 1 = 1 {3}
                                                             union all
                                                             select heatno,
                                                                    isrs,
                                                                    eletricityconsume,
                                                                    ladlecoverage,
                                                                    reposebegintime,
                                                                    arrivetime,
                                                                    refinetime,
                                                                    flexibleblowtime,
                                                                    arrivetempt,
                                                                    leavetempt
                                                             from   j#stl_lfs_optinfo where 1 = 1 {3})
                                                     group  by heatno) t8 on t.heatno = t8.heatno
                                        left   join (select s.heatno,
                                                            s.billetnum,
                                                            s.billetwgt
                                                     from   (select t.heatno,
                                                                    count(1) billetnum,
                                                                    sum(nvl(t.weight, 0)) billetwgt
                                                             from   stl_incision t
                                                             group  by heatno,
                                                                       weight
                                                             union all
                                                             select t.heatno,
                                                                    count(1) billetnum,
                                                                    sum(nvl(t.weight, 0)) billetwgt
                                                             from   y#stl_incision t
                                                             group  by heatno,
                                                                       weight) s) t9 on t.heatno = t9.heatno
                                        left   join (select y.* --LFS
                                                     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  upper(substr(checkno, 12, 1)) = 'E'
                                                                     and    upper(substr(checkno, -1)) = 'S'
                                                                    {2}
                                                                     union all
                                                                     select *
                                                                     from   j#stl_chemelement
                                                                     where  upper(substr(checkno, 12, 1)) = 'E'
                                                                    {2}
                                                                     and    upper(substr(checkno, -1)) = 'S') x) y) t10 on t.heatno =
                                                                                                                           t10.heatno
                                        left   join (select y.* --CCM
                                                     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  upper(substr(checkno, -1)) = 'S'
                                                                     and    upper(substr(checkno, 12, 2)) = 'GP'
                                                                     {2}
                                                                     union all
                                                                     select *
                                                                     from   j#stl_chemelement
                                                                     where  upper(substr(checkno, -1)) = 'S'
                                                                      {2}
                                                                     and    upper(substr(checkno, 12, 2)) = 'GP') x) y) t11 on t.heatno =
                                                                                                                               t11.heatno
                                                                        order  by t.optdate
                                                                        ";
                                                        sqlStr = lgCommon.stringFormat(sqlStr,sqlConditionList);
                                                        return sqlStr;
            }


            public static string returnSqlOfMidPackageAndSpeed(ArrayList sqlCondiotnList)
            {
                string sqlstr = "";

                sqlstr = @"select z.*
                          from (select Distinct x.Heatno,
                                                x.STATIONCODE,
                                                x.DISPOSALTIME,
                                                x.MIDLADLETEMP,
                                                to_char(x.LASTFRESHTIME,'HH24:mi') LASTFRESHTIME,
                                                y.s1castspeed,
                                                y.s2castspeed,
                                                y.s3castspeed,
                                                y.s4castspeed,
                                                y.s5castspeed,
                                                row_number() over(partition by x.HEATNO order by x.LASTFRESHTIME) xh,
                                                count(*) over(partition by x.HEATNO order by x.HEATNO) intcount
                                  from (SELECT Heatno,
                                               STATIONCODE,
                                               DISPOSALTIME,
                                               MIDLADLETEMP,
                                               min(LASTFRESHTIME) LASTFRESHTIME
                                          FROM (select HEATNO,
                                                       STATIONCODE,
                                                       DISPOSALTIME,
                                                       ROUND(greatest(midladletemp1, midladletemp2)) MIDLADLETEMP,
                                                       LASTFRESHTIME　from {0} b
                                                 where 1 = 1
                                                       {1}
                                                union
                                                select HEATNO,
                                                       STATIONCODE,
                                                       DISPOSALTIME,
                                                       ROUND(greatest(midladletemp1, midladletemp2)) MIDLADLETEMP,
                                                       LASTFRESHTIME
                                                  from J#{0}
                                                 where 1 = 1
                                                      {1}) a
                                         group by heatno,
                                                  stationcode, 
                                                  disposaltime,
                                                  MIDLADLETEMP
                                         order by heatno) x
                                  left join (select HEATNO,
                                                   STATIONCODE,
                                                   DISPOSALTIME,
                                                   s1castspeed,
                                                   s2castspeed,
                                                   s3castspeed,
                                                   s4castspeed,
                                                   s5castspeed,
                                                   LASTFRESHTIME,
                                                   ROUND(greatest(midladletemp1, midladletemp2)) MIDLADLETEMP
                                              from {0} b
                                             where 1 = 1
                                                   {1}
                                            union
                                            select HEATNO,
                                                   STATIONCODE,
                                                   DISPOSALTIME,
                                                   s1castspeed,
                                                   s2castspeed,
                                                   s3castspeed,
                                                   s4castspeed,
                                                   s5castspeed,
                                                   LASTFRESHTIME,
                                                   ROUND(greatest(midladletemp1, midladletemp2)) MIDLADLETEMP
                                              from J#{0}
                                             where 1 = 1
                                                    {1}) y on x.heatno =
                                                                                                       y.heatno
                                                                                                   and x.stationcode =
                                                                                                       y.stationcode
                                                                                                   and x.disposaltime =
                                                                                                       y.disposaltime
                                 where x.LASTFRESHTIME = y.LASTFRESHTIME
                                   and x.MIDLADLETEMP = y.MIDLADLETEMP
                                   and x.MIDLADLETEMP > 1410
                                 order by heatno, LASTFRESHTIME) z
                         where z.xh in (1, round(z.intcount / 4), round((z.intcount - 2) / 4) * 2,
                                round((z.intcount - 2) / 4) * 3,
                                round((z.intcount - 2) / 4) * 4, z.intcount)";           
                sqlstr = lgCommon.stringFormat(sqlstr,sqlCondiotnList);
                return sqlstr;
            }


            /// <summary>
            /// 获取板坯铸机恒拉表sql
            /// </summary>
            /// <param name="sqlCondition"></param>
            /// <returns></returns>
            public static string returnSqlStrCastCcmSpeed(string sqlCondition)
            {
                string sqlStr = "";
//                sqlStr = @"select a.heatno, a.stationcode, a.fixedspeed,a.starttime,  a.endtime,
//                     round((nvl(a.endtime,null) - a.starttime )*86400 / 60, 2) fixedtime, a.castflow, to_char(a.optdate,'yyyy-mm-dd HH24:mi:ss') optdate, a.info1, a.info2,a.info3, a.info4,
//                     a.info5,  a.info6, a.info7  from   stl_ccm_fixedcastspeed a  where 1 = 1 {0}";
//                sqlStr = sqlStr + @" union all select a.heatno, a.stationcode, a.fixedspeed,a.starttime,  a.endtime,
//                     round((nvl(a.endtime,null) - a.starttime )*86400 / 60, 2) fixedtime , a.castflow, to_char(a.optdate,'yyyy-mm-dd HH24:mi:ss') optdate, a.info1, a.info2,a.info3, a.info4,
//                     a.info5,  a.info6, a.info7  from   j#stl_ccm_fixedcastspeed a  where 1 = 1 {0}";
                sqlStr = @"select a.heatno, a.stationcode, round(a.fixedspeed,2) fixedspeed,a.starttime,  a.endtime,
                     case when nvl(a.endtime, null)< nvl(nvl(b.baleendtime,b.baleleavetime),b.updatetime) then
                     round((nvl(a.endtime, null) - a.starttime) * 86400 / 60, 2)
                     else round((nvl(nvl(b.baleendtime,b.baleleavetime),b.updatetime) - a.starttime) * 86400 / 60, 2) end fixedtime, a.castflow, to_char(a.optdate,'yyyy-mm-dd HH24:mi:ss') optdate, a.info1, a.info2,a.info3, a.info4,
                     a.info5,  a.info6, a.info7  from   stl_ccm_fixedcastspeed a,(select * From stl_ccm_optinfo union select * From j#stl_ccm_optinfo) b  where 1 = 1 and a.starttime < nvl(nvl(b.baleendtime, b.baleleavetime), b.updatetime) and a.heatno=b.heatno(+) {0}";
                sqlStr = sqlStr + @" union all select a.heatno, a.stationcode, round(a.fixedspeed,2) fixedspeed,a.starttime,  a.endtime,
                     case when nvl(a.endtime, null)< nvl(nvl(b.baleendtime,b.baleleavetime),b.updatetime) then
                     round((nvl(a.endtime, null) - a.starttime) * 86400 / 60, 2)
                     else round((nvl(nvl(b.baleendtime,b.baleleavetime),b.updatetime) - a.starttime) * 86400 / 60, 2) end fixedtime , a.castflow, to_char(a.optdate,'yyyy-mm-dd HH24:mi:ss') optdate, a.info1, a.info2,a.info3, a.info4,
                     a.info5,  a.info6, a.info7  from   j#stl_ccm_fixedcastspeed a,(select * From stl_ccm_optinfo union select * From j#stl_ccm_optinfo) b where 1 = 1 and a.starttime < nvl(nvl(b.baleendtime, b.baleleavetime), b.updatetime) and a.heatno=b.heatno(+) {0}";
                sqlStr = string.Format(sqlStr, sqlCondition);

                return sqlStr;
            }
        }
        /// <summary>
        /// 
        /// </summary>
     public   class RhsSqlCollection
        {
         /// <summary>
         /// 
         /// </summary>
         /// <param name="sqlCondition"></param>
         /// <returns></returns>
            public static string returnSqlOfStl_Rhs_Oxygenblowing(string sqlCondition)
            {
                string sqlStr = "select * from stl_rhs_oxygenblowing where 1 = 1 {0}";
                       sqlStr += "union all ";
                       sqlStr += "select * from j#stl_rhs_oxygenblowing where 1 = 1 {0}";
                       sqlStr = string.Format(sqlStr,sqlCondition);
                       return sqlStr;
            }



          
        }


     public class LfsSqlCollection
     {
         /// <summary>
         /// lfs 操作记录表
         /// </summary>
         /// <param name="sqlCondiotnList"></param>
         /// <returns></returns>
         public static string returnSqlOfLfs_Operation(ArrayList sqlCondiotnList)
         {
             string sqlstr = "";
             sqlstr = @"select distinct to_char(a.OPTDATE, 'yyyy-MM-dd hh24:mi') OPTDATE,
                                    a.HEATNO,
                                    decode(a.DESLAGGFLAG,'0','否','1','是') SFDZ,
                                    a.LMLTEMPT,
                                    substr(a.STATIONCODE,2,1) ||'LF'||'-'||a.WSID STATIONCODE,
                                    b.MaxSPvalue,
                                    b.MinSPVALUE,
                                    b.AvgSPVALUE,
                                    a.LFSNO,
                                    a.PLANSTEEL,
                                    a.POTNO, 
                                    a.POTWRAPSTATE,
                                    a.POTAGE,
                                    decode(substr(a.shiftcode, 1, 1),
                                           '1',
                                           '早',
                                           '2',
                                           '中',
                                           '3',
                                           '晚') BC,
                                    decode(substr(a.shiftcode, 2, 1),
                                           '1',
                                           '甲',
                                           '2',
                                           '乙',
                                           '3',
                                           '丙',
                                           '4',
                                           '丁') BB,
                                    nvl(u.PFBALEWGT,0)-nvl(u.BALELEAVEWGT,0) MOLSTLWGT,
                                    a.RESIDUETHICKNESS,
                                    to_char(a.ARRIVETIME, 'hh24:mi') ARRIVETIME,
                                    to_char(a.LEAVETIME, 'hh24:mi') LEAVETIME,
                                    a.ARRIVEWGT,
                                    a.LEAVEWGT,
                                    a.ARRIVEO2,
                                    a.LEAVEO2,
                                    '' WAL,
                                    '' WCA,
                                    '' SDDW,
                                    '' GLMB,
                                    to_char(a.beginblowar, 'hh24:mi') SELESTARTTIME,
                                    to_char(a.endblowar, 'hh24:mi') SELEENDTIME,
                                    a.SELETIME,
                                    a.ARRIVETEMPT,a.LEAVETEMPT,
                                    '' PPM,
                                    a.BLOWARPRESS,
                                    a.ARFLUX,
                                    '' WATERP,
                                    '' WATERFLOW,
                                    to_char(a.REPOSEBEGINTIME, 'hh24:mi') REPOSEBEGINTIME,
                                    a.REPOSETIME,
                                    a.FLEXIBLEBLOWTIME,
                                    v.PLAN_LINES,
                                    /*v.CAST_ID || '-' || v.cast_seq CAST_ID,*/
                                    u.heat_seq CAST_ID,
                                    a.LEAVETEMPT PFBALETEMPT,
                                    decode(a.DESLAGGFLAG,'0','否','1','是') IFSLAG,
                                    I.POTDISTINCTION POTDISTINCTION,
                                    round((a.endblowar - a.beginblowar) * 24 * 60) YLZQ,
                            to_char(a.REPOSECLOSETIME,'hh24:mi') QDTIME,
                                    a.DISPOSALTIME,
                                    a.bzhatime,
                                    a.thermometer,
                                    a.sampler,
                                    a.reason18,
                                    a.modifier, 
                                    a.memo,
                                    h.arbtempt castempt,
                                    a.refinetime, 
                                    a.nb_wgt,a.v_wgt,a.cu_wgt,a.ni_wgt,a.b_wgt,a.mo_wgt,a.optpersonnel,
                                    
                                    /*max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.C end) over(partition by a.HEATNO) C_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.SI end) over(partition by a.HEATNO) SI_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.MN end) over(partition by a.HEATNO) MN_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.P end) over(partition by a.HEATNO) P_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.S end) over(partition by a.HEATNO) S_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.ALS end) over(partition by a.HEATNO) ALS_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.Alt end) over(partition by a.HEATNO) ALT_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.Ca end) over(partition by a.HEATNO) CA_1, 
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.Nb end) over(partition by a.HEATNO) NB_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.V end) over(partition by a.HEATNO) V_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.Cu end) over(partition by a.HEATNO) Cu_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.Ni end) over(partition by a.HEATNO) NI_1,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = 1 then w.Mo end) over(partition by a.HEATNO) MO_1,  */  
 
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.C end) over(partition by a.HEATNO) C_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.si end) over(partition by a.HEATNO) SI_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.MN end) over(partition by a.HEATNO) MN_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.P end) over(partition by a.HEATNO) P_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.S end) over(partition by a.HEATNO) S_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.ALS end) over(partition by a.HEATNO) ALS_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.Alt end) over(partition by a.HEATNO) ALT_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.Ca end) over(partition by a.HEATNO) CA_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.Nb end) over(partition by a.HEATNO) NB_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.V end) over(partition by a.HEATNO) V_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.Cu end) over(partition by a.HEATNO) Cu_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.Ni end) over(partition by a.HEATNO) NI_1,
                                    max(case when a.HEATNO = n.HEATNO and n.xh = n.cnt then n.Mo end) over(partition by a.HEATNO) MO_1,  
        
                                    max(case when a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.C end) over(partition by a.HEATNO) C_2,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.SI end) over(partition by a.HEATNO) SI_2,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.MN end) over(partition by a.HEATNO) MN_2,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.P end) over(partition by a.HEATNO) P_2,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.S end) over(partition by a.HEATNO) S_2,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.ALS end) over(partition by a.HEATNO) ALS_2,
                                    max(case When a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.Alt end) over(partition by a.HEATNO) ALT_2,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = round(w.cnt / 2) then w.Ca end) over(partition by a.HEATNO) CA_2,
                                    
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.C end) over(partition by a.HEATNO) C_3,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.SI end) over(partition by a.HEATNO) SI_3,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.MN end) over(partition by a.HEATNO) MN_3,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.P end) over(partition by a.HEATNO) P_3,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.S end) over(partition by a.HEATNO) S_3,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.ALS end) over(partition by a.HEATNO) ALS_3,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.ALT end) over(partition by a.HEATNO) ALT_3,
                                    max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.Ca end) over(partition by a.HEATNO) CA_3,
                                    
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.c end) over(partition by a.heatno) C_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.SI end) over(partition by a.heatno) SI_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.MN end) over(partition by a.heatno) MN_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.P end) over(partition by a.heatno) P_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.S end) over(partition by a.heatno) S_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.ALS end) over(partition by a.heatno) ALS_4,
                                    
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.NI end) over(partition by a.heatno) Ni_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.MO end) over(partition by a.heatno) Mo_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.NB end) over(partition by a.heatno) Nb_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.V end) over(partition by a.heatno) V_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.CR end) over(partition by a.heatno) Cr_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.CU end) over(partition by a.heatno) Cu_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.ALT end) over(partition by a.heatno) ALT_4,
                                    max(case when a.heatno = t4.heatno and t4.xh = 1  then t4.CA end) over(partition by a.heatno) CA_4,
                                    GetStandardVaule(a.HEATNO,t4.MN,t4.NI,t4.MO,t4.NB,t4.V,t4.ALS,t4.CR,t4.CU) CFFC
                      from (select *
                              from stl_LFS_OPTINFO
                             where {0}
                            union
                            select * from J#stl_LFS_OPTINFO where {0}) a,
                           
                           (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
                                      from (select *
                                              from stl_CHEMELEMENT
                                             where upper(SUBSTR(CHECKNO, 12, 1)) = 'E'
                                               and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                               
                                               {1}
                                            union
                                            select *
                                              from J#stl_CHEMELEMENT
                                             where upper(SUBSTR(CHECKNO, 12, 1)) = 'E'
                                               and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                               
                                               {1}) x) y
                             where y.xh in (1, round(y.cnt / 2), y.cnt)) w,
                           (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
                                          from (select *
                                                  from stl_CHEMELEMENT
                                                 where upper(SUBSTR(CHECKNO, 12, 2)) = 'DQ'
                                                   and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                   {1}
                                                union
                                                select *
                                                  from J#stl_CHEMELEMENT
                                                 where upper(SUBSTR(CHECKNO, 12, 2)) = 'DQ'
                                                   and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                    {1}
                                                    ) x) y
                                 where y.xh in (1, round(y.cnt / 2), y.cnt)) n,
                           (select y.*
                              from (select x.*,
                                           row_number() over(partition by x.HEATNO, substr(assaytypecode, 1, 1) order by x.ASSAYTYPECODE desc) xh,
                                           substr(assaytypecode, 1, 1) typecode
                                      from (select *
                                              from stl_CHEMELEMENT
                                             where  upper(SUBSTR(CHECKNO, -1)) = 'S'
                                               and upper(SUBSTR(CHECKNO, 12, 2)) = 'GP'
                                               {2}
                                            union
                                            select *
                                              from J#stl_CHEMELEMENT
                                             where  upper(SUBSTR(CHECKNO, -1)) = 'S'
                                               and upper(SUBSTR(CHECKNO, 12, 2)) = 'GP'
                                             {2}) x) y
                             where y.xh = 1) t4,
                           
                           (select *
                              from stl_CCM_OPTINFO
                             where {3}
                            union
                            select * from J#stl_CCM_OPTINFO where {3}) u,
                            
                            (select HEATNO, maxSPvalue, MinSPVALUE, avgSPVALUE
                              from (select max(SAMPLINGVALUE) maxSPvalue,
                                           min(SAMPLINGVALUE) MinSPVALUE,
                                           round(avg(SAMPLINGVALUE), 2) avgSPVALUE,
                                           HEATNO
                                      from stl_tempsampling where  substr(samplingdepict,1,4)='中包温度'
                                      {4}
                                     group by HEATNO
                                    union
                                    select max(SAMPLINGVALUE) maxSPvalue,
                                           min(SAMPLINGVALUE) MinSPVALUE,
                                           round(avg(SAMPLINGVALUE), 2) avgSPVALUE,
                                           HEATNO
                                      from J#stl_tempsampling where  substr(samplingdepict,1,4)='中包温度'
                                     {4}
                                     group by HEATNO)) b,
                           
                           (select *
                              from stl_CAS_OPTINFO
                             where {5}
                            union
                            select * from J#stl_CAS_OPTINFO where {5}) h,
                            (select *
                              from stl_BOF_OPTINFO
                             where {6}
                            union
                            select * from J#stl_BOF_OPTINFO where {6}) I,
                           (select *
                              from ppc_steel_heat where 1 = 1 {7}
                            union
                            select * from J#ppc_steel_heat where 1 = 1 {7}) v
                     where a.HEATNO = u.HEATNO(+)
                       and a.Heatno = N.HEATNO(+)
                       and a.HEATNO = w.HEATNO(+)
                       and a.HEATNO = v.HEATNo(+)
                       and a.HEATNO = t4.HEATNO(+)
                       and a.heatno = h.heatno(+)
                       and a.heatno = b.HEATNO(+)
                       and a.heatno = I.HEATNO(+)
                       order by OPTDATE";
                       sqlstr = lgCommon.stringFormat(sqlstr, sqlCondiotnList);
                      return sqlstr;
             
         }
 
     }
    }
}