XinYu_LG2MES/ClientSolution/Core.Mes.Client.Common/lgSqlCollection/SqlCollectionVds.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>
    /// vds sql 集合
    /// </summary>
   public  class SqlCollectionVds
    {
       /// <summary>
       /// vds report sql
       /// </summary>
       public class VdsReportSqlCollection
       {
           /// <summary>
           /// vds 操作记录表
           /// </summary>
           /// <param name="sqlConditionList"></param>
           /// <returns></returns>
           public static string returnSqlOfVdsReportDailyOperation(ArrayList sqlConditionList)
           {
                                               string sqlStr = @"select distinct to_char(a.OPTDATE, 'yyyy-MM-dd hh24:mi') OPTDATE,
                                                a.HEATNO,
                                                decode(substr(a.shiftcode, 1, 1),
                                                       '1',
                                                       '早',
                                                       '2',
                                                       '中',
                                                       '3',
                                                       '晚') BC,
                                                decode(substr(a.shiftcode, 2, 1),
                                                       '1',
                                                       '甲',
                                                       '2',
                                                       '乙',
                                                       '3',
                                                       '丙',
                                                       '4',
                                                       '丁') BB,
                                                a.LMLTEMPT,
                                                b.MaxSPvalue,
                                                b.MinSPVALUE,
                                                b.AvgSPVALUE,
                                                a.PLANSTEEL,
                                                a.HEATPROCESSNO,
                                                to_char(a.ARRIVETIME,'hh24:mi:ss')ARRIVETIME,
                                                I.POTNO,
                                                I.POTDISTINCTION,
                                                a.WSID,
                                                a.RESIDUETHICKNESS,
                                                a.CLEARENCELHEIGHT,
                                                a.ARFTEMPT,
                                                to_char(a.VACBEGINTIME,'hh24:mi')VACBEGINTIME,
                                                a.ARRIVEH,
                                                a.VACUUMPRESS VAC_MIN,
                                                to_char(a.HYDGENSTARTTIME,'hh24:mi')HYDGENSTARTTIME,
                                                a.ARBTEMPT ARBTEMPT_A,
                                                to_char(a.VACLOSETIME,'hh24:mi')VACLOSETIME,
                                                a.LEAVEH,
                                                to_char(a.HYDGENENDTIME,'hh24:mi')HYDGENENDTIME,
                                                '' PROPHASE,
                                                '' INTERIM,
                                                '' LATE,
                                                a.FLEXIBLEBLOWTIME,
                                                --a.VACUUMPRESS,
                                                FLOOR(nvl(a.VAC_KEEP_TIME / 60, 0)) VAC_KEEP_TIME,
                                                a.VAC_TIME VACUUMTIME,
                                                a.STEAM_TEM,
                                                a.STEAM_PRES,
                                                a.STEAM_FLUX,
                                                to_char(a.LEAVETIME,'hh24:mi') LEAVETIME,
                                                p.PLAN_LINES,
                                                u.PFBALETEMPT,
                                                a.ARBTEMPT,
                                                u.HEAT_SEQ HEAT_NUM,
                                                a.MEMO,
                                                a.blowprocess,
                                                a.replacefjg,
                                                a.MEMO ex_mome,
                                                --a.sppm,
                                                a.thermometer,
                                                a.sampler,
                                                a.replacedesiccant,
                                                a.blowisok,
                                                a.replacegd, 
                                                a.steamconsume,
                                                a.arconsume,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then   w.c  end) over(partition by a.HEATNO) Y_C,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then  w.si end) over(partition by a.HEATNO) Y_SI,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.MN  end) over(partition by a.HEATNO) Y_MN,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then  w.P  end) over(partition by a.HEATNO) Y_P,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.S  end) over(partition by a.HEATNO) Y_S,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then  w.ALS  end) over(partition by a.HEATNO) Y_ALS,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.NB  end) over(partition by a.HEATNO) Y_NB,
                                                max(case  when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.V  end) over(partition by a.HEATNO) Y_V,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.TI  end) over(partition by a.HEATNO) Y_TI,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.CU  end) over(partition by a.HEATNO) Y_CU,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then  w.N  end) over(partition by a.HEATNO) Y_N,
                                                max(case  when a.HEATNO = w.HEATNO and w.xh = w.cnt then  w.B  end) over(partition by a.HEATNO) Y_B,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.c  end) over(partition by a.HEATNO) C_C,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.SI  end) over(partition by a.HEATNO) C_SI,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.MN  end) over(partition by a.HEATNO) C_MN,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.P  end) over(partition by a.HEATNO) C_P,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.S  end) over(partition by a.HEATNO) C_S,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.ALS   end) over(partition by a.HEATNO) C_ALS,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.NB   end) over(partition by a.HEATNO) C_NB,
                                                max(case  when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.V  end) over(partition by a.HEATNO) C_V,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then  t4.TI  end) over(partition by a.HEATNO) C_TI,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.CU  end) over(partition by a.HEATNO) C_CU,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.N  end) over(partition by a.HEATNO) C_N,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.B  end) over(partition by a.HEATNO) C_B,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.N  end) over(partition by a.HEATNO) C_NI,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.B  end) over(partition by a.HEATNO) C_MO,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.N  end) over(partition by a.HEATNO) C_CR,
 				                                GetStandardVaule(a.HEATNO,t4.MN,t4.NI,t4.MO,t4.NB,t4.V,t4.ALS,t4.CR,t4.CU) CFFC,
                                                '' ETGYEXRECORD,a.OPTPERSONNEL,REFINETIME,
                                                max(case when a.HEATNO = t4.HEATNO and t4.xh = t4.cnt then t4.S  end) over(partition by a.HEATNO)*10000 SPPM,
                                                max(case when a.HEATNO = w.HEATNO and w.xh = w.cnt then w.S  end) over(partition by a.HEATNO)*10000 LF_SPPM,
                                                HYDROGENPOLE,CHANGEFJG,VAC_END_TEMP
                                  from (select *
                                          from stl_vds_optinfo  where disposaltime = '01' {0}
                                        union
                                        select * from j#stl_vds_optinfo  where disposaltime = '01' {0} ) a,
                                        (select *
                                          from stl_BOF_OPTINFO where 1 = 1 {1}
                                        union
                                        select * from J#stl_BOF_OPTINFO where 1 = 1 {1}) I,
                                        (select *
                                          from STL_CCM_OPTINFO   where disposaltime = '01' {1}
                                        union 
                                        select * from J#stl_CCM_OPTINFO   where disposaltime = '01' {1} ) 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)='中包温度'
                                                  {2}
                                                 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)='中包温度' {2}
                                                 group by HEATNO)) b,
                                       (select *
                                          from ppc_steel_heat where 1 = 1 {3}
                                        union  
                                        select * from J#ppc_steel_heat  where 1 = 1 {3}) p,
                                       (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 t.*
                                                          from stl_chemelement t where upper(SUBSTR(CHECKNO, 12, 1)) = 'I'
                                                           and upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                          {4}
                                                        union
                                                        select t.*
                                                          from j#stl_CHEMELEMENT t where upper(SUBSTR(CHECKNO, 12, 1)) = 'I'
                                                           and upper(SUBSTR(CHECKNO, -1)) = 'S' 
                                                          {4}
                                                        ) 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 t.*
                                                          from stl_CHEMELEMENT t
                                                         where upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                           and upper(SUBSTR(CHECKNO, 12, 2)) = 'GP'
                                                           {4}
                                                        union
                                                        select t.*
                                                          from J#stl_CHEMELEMENT t
                                                         where upper(SUBSTR(CHECKNO, -1)) = 'S'
                                                           and upper(SUBSTR(CHECKNO, 12, 2)) = 'GP'
                                                          {4}
                                                        ) x) y where y.xh = y.cnt
                                         ) t4 
                                 where a.HEATNO = p.HEATNO(+)
                                   and a.HEATNO = w.HEATNO(+)
                                   and a.HEATNO = t4.HEATNO(+)
                                   and a.HEATNO = u.HEATNO(+)
                                   and a.heatno = b.HEATNO(+)
                                   and a.heatno = I.HEATNO(+)
                                   --and t4.xh = t4.cnt
                                 order by OPTDATE";
               sqlStr = lgCommon.stringFormat(sqlStr,sqlConditionList);
               return sqlStr;
           }
       }
    }
}