using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreFS.CA06;
using System.Data;
using System.Collections;
using Core.Mes.Client.Common;
namespace Core.LgMes.Client.lgServiceMgt.LgSqlCollection.LgReportSql
{
///
/// vds sql 集合
///
public class SqlCollectionVds
{
///
/// vds report sql
///
public class VdsReportSqlCollection
{
///
/// vds 操作记录表
///
///
///
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,
p.HEAT_NUM,
a.MEMO,
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
from (select *
from stl_vds_optinfo where 1 = 1 {0}
union
select * from j#stl_vds_optinfo where 1 = 1 {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 1 = 1 {1}
union
select * from J#stl_CCM_OPTINFO where 1 = 1 {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
) 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(+)
order by OPTDATE";
sqlStr = lgCommon.stringFormat(sqlStr,sqlConditionList);
return sqlStr;
}
}
}
}