using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreFS.CA06;
using System.Data;
using System.Collections;
using Core.Mes.Client.Common;
namespace Core.LgMes.Client.lgServiceMgt.LgSqlCollection.LgReportSql
{
public class SqlCollectionBof
{
///
/// 查询转炉炉前铁水信息
///
///
///
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;
}
///
/// 返回转炉操作记录报表查询
///
///
///
public static string returnSqlOfBofOperatioReport(ArrayList sqlConditionList)
{
string sqlStr = "";
sqlStr = @"select distinct t.heatno,
t4.aim_grade_code plansteel,
t.stationcode,
t.sage,
t.gage,
t.o2consume,
nvl(t.ironpotwgt, '0') ironpotwgt,
t.wsteelwgt,
t.pigironwgt,
/*case when t6.baleleavewgt is not null then round(((nvl(t.ironpotwgt, '0')+nvl(t.wsteelwgt, '0')+nvl(t.pigironwgt, '0')))*1000/(t6.pfbalewgt - t6.baleleavewgt),2) else 0 end iron_sum,*/
case when t6.baleleavewgt is not null then round(((nvl(t.ironpotwgt, '0')+nvl(t.wsteelwgt, '0')+nvl(t.pigironwgt, '0')))*1000/(decode(t6.pfbalewgt - t6.baleleavewgt,null,1,0,1,t6.pfbalewgt - t6.baleleavewgt)),2) else 0 end iron_sum,
t.brpstime,
t.potno,
t.potdistinction,
t.potwrapstate,
t6.pfbalewgt - t6.baleleavewgt tappingwgt,
decode(substr(t.shiftcode, 1, 1), '1', '早', '2', '中', '3', '晚') bc,
decode(substr(t.shiftcode, 2, 1), '1', '甲', '2', '乙', '3', '丙', '4', '丁') bb,
decode(blkredeffects, '0', '失败', '1', '成功', '2', '未投', '3', '一般') blkredeffects,
t.subage,
t.changestarttime,
t.openoxygentime,
round(t.tappingmaxangle,2) tappingmaxangle,round(t.psmaxangle,2) psmaxangle,
t.irontemperature,
t.wsteelwgt_fg,
t.yakuaiwgt_fg,
t.zhasteelwgt_fg,
t.ironwgt_fg,
t.broken_fg,
t.terminusc,
t.terminuso2,
(select nvl(temperature,0)
from (select *
from (SELECT *
FROM stl_bof_subsampling
WHERE samplingtype = 'TSC'
union
SELECT *
FROM j#stl_bof_subsampling
WHERE samplingtype = 'TSC')
order by samplingdate desc)
WHERE heatno = t.heatno
AND rownum = 1) b1temperature,
(select nvl(temperature,0)
from (select *
from (SELECT *
FROM stl_bof_subsampling
WHERE samplingtype = 'TSO'
union
SELECT *
FROM j#stl_bof_subsampling
WHERE samplingtype = 'TSO')
order by samplingdate desc)
WHERE heatno = t.heatno
AND rownum = 1) b2temperature,
nvl(t.n2consume, 0) + nvl(t.topn2consume, 0) n2sum,
t.irontemperature,
'0.9' o2press,
nvl(t.b2time, 0) + nvl(t.b3time, 0) + nvl(t.b4time, 0) +
nvl(t.b5time, 0) dctime,
to_char(t.changestarttime, 'HH24:mi') changestarttime,
to_char(t.openoxygentime, 'HH24:mi') openoxygentime,
round(nvl(t.supplyoxygentime / 60, 0), 1) supplyoxygentime,
round(t.supplyoxygentime, 1) supplyoxygentime_s,
t4.plan_lines plan_lines,
t.terminustept tappingtemperature,
t.tappingtime,
to_char(t.tappingstarttime, 'HH24:mi') tappingstarttime,
max(case
when t.heatno = t2.heatno
and t2.xh = t2.cnt then
t2.c
end) over(partition by t.heatno) f_c,
max(case
when t.heatno = t2.heatno
and t2.xh = t2.cnt then
t2.mn
end) over(partition by t.heatno) f_mn,
max(case
when t.heatno = t2.heatno
and t2.xh = t2.cnt then
t2.si
end) over(partition by t.heatno) f_si,
max(case
when t.heatno = t2.heatno
and t2.xh = t2.cnt then
t2.s
end) over(partition by t.heatno) f_s,
max(case
when t.heatno = t2.heatno
and t2.xh = t2.cnt then
t2.p
end) over(partition by t.heatno) f_p,
max(case
when t.heatno = t2.heatno
and t2.xh = t2.cnt then
t2.als
end) over(partition by t.heatno) f_als,
max(case
when t.molirnsource = t10.heatno then
t10.mn
end) over(partition by t.molirnsource) r_mn,
max(case
when t.molirnsource = t10.heatno then
t10.c
end) over(partition by t.molirnsource) r_c,
max(case
when t.molirnsource = t10.heatno then
t10.p
end) over(partition by t.molirnsource) r_p,
max(case
when t.molirnsource = t10.heatno then
t10.s
end) over(partition by t.molirnsource) r_s,
max(case
when t.molirnsource = t10.heatno then
t10.si
end) over(partition by t.molirnsource) r_si,
max(case
when t.heatno = t9.heatno
and t9.xh = t9.cnt then
t9.mn
end) over(partition by t.heatno) z_mn,
max(case
when t.heatno = t9.heatno
and t9.xh = t9.cnt then
t9.c
end) over(partition by t.heatno) z_c,
max(case
when t.heatno = t9.heatno
and t9.xh = t9.cnt then
t9.p
end) over(partition by t.heatno) z_p,
max(case
when t.heatno = t9.heatno
and t9.xh = t9.cnt then
t9.s
end) over(partition by t.heatno) z_s,
max(case
when t.heatno = t9.heatno
and t9.xh = t9.cnt then
t9.si
end) over(partition by t.heatno) z_si,
case when max(case
when t.molirnsource = t10.heatno then
t10.mn
end) over(partition by t.molirnsource) is not null then
round(max(case
when t.heatno = t9.heatno and t9.xh = t9.cnt then
t9.mn
end) over(partition by t.heatno)/max(case
when t.molirnsource = t10.heatno then
decode(t10.mn,null,1,0,1) --t10.mn 铁水mn元素有可能为0
end) over(partition by t.molirnsource),2) else 0 end mn_harvest_rate,
t4.cast_id ccmno,
(t4.cast_id) || '-' || (t4.cast_seq) ccmno,
t.memo,
to_char(t.optdate, 'yyyy-MM-dd hh24:mi') optdisp,
t.remark2
from (select *
from stl_bof_optinfo
where 1 = 1
{0}
union all
select *
from j#stl_bof_optinfo
where 1 = 1
{0}) t
left join (select y.*
from (select x.*,
row_number() over(partition by x.heatno order by x.assaytypecode) xh
from (select *
from stl_chemelement
where upper(substr(assaytypecode, 1, 1)) = 'C'
{1}
union all
select *
from j#stl_chemelement
where upper(substr(assaytypecode, 1, 1)) = 'C'
{1}) x) y) t1 on t.heatno =
t1.heatno
left join (select y.*
from (select x.*,
count(1) over(partition by x.heatno) cnt,
row_number() over(partition by x.heatno order by x.assaytypecode desc, x.assaydate desc) xh,
substr(x.assaytypecode, 1, 1) gw
from (select *
from stl_chemelement
where 1 = 1
{1}
and upper(substr(checkno, -1)) = 'S'
and upper(substr(checkno, 12, 2)) = 'GP'
union all
select *
from j#stl_chemelement
where 1 = 1
{1}
and upper(substr(checkno, -1)) = 'S'
and upper(substr(checkno, 12, 2)) = 'GP') x) y) t2 on t2.heatno =
t.heatno
left join (select y.*
from (select x.*,
count(1) over(partition by x.heatno) cnt,
row_number() over(partition by x.heatno order by x.assaytypecode desc, x.assaydate desc) xh,
substr(x.assaytypecode, 1, 1) gw
from (select *
from stl_chemelement
where 1 = 1
{1}
and upper(substr(checkno, 12, 1)) = 'C'
and upper(substr(checkno, -1)) = 'I'
and upper(substr(checkno, 13, 1)) <> 'P'
union all
select *
from j#stl_chemelement
where 1 = 1
{1}
and upper(substr(checkno, 12, 1)) = 'C'
and upper(substr(checkno, -1)) = 'I'
and upper(substr(checkno, 13, 1)) <> 'P') x) y) t3 on t3.heatno =
t.heatno
left join (select y.*
from (select x.*,
count(1) over(partition by x.heatno) cnt,
row_number() over(partition by x.heatno order by x.assaytypecode) xh,
substr(x.assaytypecode, 1, 1) gw
from (select *
from stl_chemelement
where 1 = 1
{1}
and upper(substr(checkno, 12, 1)) = 'C'
and upper(substr(checkno, -1)) = 'S'
union all
select *
from j#stl_chemelement
where 1 = 1
{1}
and upper(substr(checkno, 12, 1)) = 'C'
and upper(substr(checkno, -1)) = 'S') x) y) t9 on t9.heatno =
t.heatno
left join (select *
from stl_chemelement where 1 = 1 {1}
union all
select * from j#stl_chemelement where 1 = 1 {1}) t10 on t10.heatno =
t.molirnsource
left join (select *
from ppc_steel_heat where 1 = 1 {3}
union all
select * from j#ppc_steel_heat where 1 = 1 {3}) t4 on t.heatno =
t4.heatno
/*left join (select *
from ppc_steel_heat where 1 = 1 {3}
union all
select * from j#ppc_steel_heat where 1 = 1 {3}) t5 on t.heatno =
t5.heatno*/
left join (select heatno,
pfbalewgt,
baleleavewgt,
isrs,
mwrapno,
billetfixsize
from stl_ccm_optinfo
where 1 = 1
{2}
union all
select heatno,
pfbalewgt,
baleleavewgt,
isrs,
mwrapno,
billetfixsize
from j#stl_ccm_optinfo
where 1 = 1
{2}) t6 on t.heatno = t6.heatno
order by optdisp
";
sqlStr = lgCommon.stringFormat(sqlStr, sqlConditionList);
return sqlStr;
}
///
/// 返回转炉出钢c报表查询sql
///
///
///
public static string returnSqlOfBofTapping(ArrayList sqlConditionList)
{
string sqlStr = "";
sqlStr = @" select distinct t.HEATNO, --炉号
t4.aim_grade_code PLANSTEEL, --钢种
case substr(t.SHIFTCODE, 2, 1)--班组
when '1' then
'甲'
when '2' then
'乙'
when '3' then
'丙'
when '4' then
'丁'
end BB,
case substr(t.SHIFTCODE, 1, 1)--班次
when '1' then
'早'
when '2' then
'中'
when '3' then
'夜'
end BC,
nvl(t.B2TIME,0) B2TIME, --2#吹时间(s)
nvl(t.B3TIME,0) B3TIME, --3#吹时间(s)
nvl(t.B4TIME,0) B4TIME, --4#吹时间(s)
nvl(t.B5TIME,0) B5TIME, --5#吹时间(s)
--LD2(第二次取样)
max(case
when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
t3.Mn
end) over(partition by t.HEATNO) Z_MN,
max(case
when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
t3.C
end) over(partition by t.HEATNO) Z_C,
max(case
when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
t3.P
end) over(partition by t.HEATNO) Z_P,
max(case
when t.HEATNO = t3.HEATNO and t3.xh = t3.cnt then
t3.S
end) over(partition by t.HEATNO) Z_S,
to_char(t.optdate, 'yyyy-MM-dd hh24:mi') OPTDISP --操作时间
from (select *
from STL_bof_optinfo
where 1 = 1
{0}
union
select *
from J#STL_bof_optinfo
where 1 = 1
{0}
) t
left join (select y.*
from (select x.*,
count(1) over(partition by x.HEATNO) cnt,
row_number() over(partition by x.HEATNO order by x.ASSAYTYPECODE asc, x.assaydate asc) xh,
substr(x.assaytypecode, 1, 1) gw
from (select *
from STL_CHEMELEMENT
where 1 = 1
and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
and upper(SUBSTR(CHECKNO, -1)) = 'S'
{1}
union
select *
from J#STL_CHEMELEMENT
where 1 = 1 {1}
and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
and upper(SUBSTR(CHECKNO, -1)) = 'S') x) y ) t3 on t.heatno = t3.heatno
left join (select *
from ppc_steel_heat where 1 = 1 {2}
union
select * from J#ppc_steel_heat where 1 = 1 {2}) t4 on t.heatno = t4.heatno
order by OPTDISP";
sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr,sqlConditionList);
return sqlStr;
}
///
/// 终点MN考核报表
///
///
///
public static string ReturnSqlOfBofChecnNmReport(ArrayList sqlConditonList)
{
string sqlStr = "";
sqlStr = @"select distinct t.HEATNO,--炉号
t.STATIONCODE, --岗位编号
substr(t.SHIFTCODE, 2, 1) SHIFTCODE, --班组(甲、乙、丙、丁)
max(case
when t.HEATNO = t1.HEATNO and t1.xh = t1.cnt then
t1.Mn
end) over(partition by t.HEATNO) Z_MN,
t2.stdmin, --最小值(内控)
t2.stdmax, --最大值(内控)
to_char(t.optdate, 'yyyy-MM-dd') OPTDISP --日期
from (select * from stl_bof_optinfo where 1 = 1
{0}
union all select * from j#stl_bof_optinfo where 1 = 1
{0}
) t
left join (select y.*
from (select x.*,
count(1) over(partition by x.HEATNO) cnt,
row_number() over(partition by x.HEATNO order by x.ASSAYTYPECODE asc, x.assaydate asc) xh,
substr(x.assaytypecode, 1, 1) gw
from (select *
from STL_CHEMELEMENT
where 1 = 1 {1}
and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
and upper(SUBSTR(CHECKNO, -1)) = 'S'
union all
select *
from J#STL_CHEMELEMENT
where 1 = 1 {1}
and upper(SUBSTR(CHECKNO, 12, 1)) = 'C'
and upper(SUBSTR(CHECKNO, -1)) = 'S') x) y) t1 on t1.heatno = t.heatno
left join ( select distinct t3.gycode,t4.itemcode,t4.stdmin,t4.stdmax --decode(t1.itemcode,'Mn',t1.stdmin,'') MN_Min, decode(t1.itemcode,'Mn',t1.stdmax,'') MN_Max
from scm_sortcode_chem t3, scm_standard_chem t4
where t3.cic = t4.cic
and t3.stdstyle = '1'
and t4.itemcode= 'Mn' {2}) t2 on t2.gycode = t.heatno
order by substr(OPTDISP,9,2) ";
sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr,sqlConditonList);
return sqlStr;
}
///
/// 钢铁物料消耗报表
///
///
public static string ReturnSqlOfSteelAdditStats(ArrayList sqlConditionList)
{
string sqlStr = "";
sqlStr = @"select A.HEATNO, --炉号
A.station STATIONCODE, --岗位编码
nvl(round(B.CPL, 3), 0) CPL, --合格铸坯量（T）
case
when (nvl(A.PFBALEWGT, 0) - nvl(A.BALELEAVEWGT, 0)) > 0 then
round((nvl(A.PFBALEWGT, 0) - nvl(A.BALELEAVEWGT, 0)), 3)
else
0
end GSL, --GSL(合格钢水量) = (上台大包重量-下台大包重量)
(select substr(mes_lg_common.getlgshift1(A.RecFlag), 2, 1) from dual) SHIFTCODE,--班组
case
when to_char(A.RecFlag, 'yyyy-MM-dd HH24:mi') between '{2}' and '{3}' then
'1'
else
'0'
end BECURR --1#吹开始时刻(操作时间)
from (select *
from (select t.*,
row_number() over(partition by HEATNO order by RecFlag) XH
from (select t.recflag,station, t2.*
from (select t.heatno,
case
when B1STARTTIME is not null then
B1STARTTIME
else
OPTDATE
end RecFlag,t.stationcode station
from STL_bof_optinfo t
where 1 = 1 {0}
union all
select t.heatno,
case
when B1STARTTIME is not null then
B1STARTTIME
else
OPTDATE
end RecFlag,t.stationcode station
from J#STL_bof_optinfo t
where 1 = 1 {0}) t,
(select *
from STL_ccm_optinfo where 1 = 1 {1}
union
select * from J#STL_Ccm_Optinfo where 1 = 1 {1}) t2
where t.heatno = t2.heatno) t)
where XH = 1) A,
(select distinct substr(CHARGE_NO, 1, 10) HEATNO,
sum(nvl(SLAB_WGT, 0)) over(partition by substr(CHARGE_NO, 1, 10)) CPL
from (select CHARGE_NO, (SLAB_WGT/1000) SLAB_WGT --合格铸坯量(T)
from stl_incision_view
where 1 = 1 and to_char(to_date(substr(SLAB_CUT_DTIME,1,8),'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd') between '{4}' and '{5}'
) t) B
where A.HEATNO = B.HEATNO(+) ";
sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr,sqlConditionList);
return sqlStr;
}
public static string ReturnSqlOfBofQueryInfo(ArrayList sqlConditionList)
{
string sqlStr = "";
sqlStr = @"select distinct b.HEATNO, --炉号
b.STATIONCODE, --岗位编号
substr(b.SHIFTCODE, 2, 1) SHIFTCODE,--班组(甲、乙、丙、丁)
nvl(b.IRONPOTWGT, 0) TSL, --装入铁水量
nvl(b.PIGIRONWGT, 0) STL, --装入生铁量（T）
nvl(b.WSTEELWGT, 0) FGL, --装入废钢量（T）
nvl(b.RSWGT, 0) HLL, --回炉重量（T）
nvl(h.jmwgt,0) HJL, --加入合金量(Kg)
to_char(b.CHANGESTARTTIME,'yyyy-mm-dd hh24:mi:ss') starttime, --兑铁开始时刻
to_char(b.TAPPINGENDTIME,'yyyy-mm-dd hh24:mi:ss') endtime, --出钢结束时刻
nvl((b.TAPPINGENDTIME-b.CHANGESTARTTIME )*24*60,0) time, --作业时间(min)
case
when to_char(RecFlag, 'yyyy-MM-dd HH24:mi') between '{0}' and '{1}' then
'1'
else
'0'
end BECURR --1#吹开始时刻(操作时间)
from (select *
from (select t.*,
row_number() over(partition by t.HEATNO order by t.RecFlag) XH
from (select t.*,
case
when B1STARTTIME is not null then
B1STARTTIME
else
OPTDATE
end RecFlag
from STL_BOF_OPTINFO t
where 1 = 1
and optdate >= trunc(to_date('{2}','yyyy-mm-dd'))
and optdate < trunc(to_date('{3}','yyyy-mm-dd')) + 1
union all
select t.*,
case
when B1STARTTIME is not null then
B1STARTTIME
else
OPTDATE
end RecFlag
from J#STL_BOF_OPTINFO t
where 1 = 1
and optdate >= trunc(to_date('{2}','yyyy-mm-dd'))
and optdate < trunc(to_date('{3}','yyyy-mm-dd')) + 1 ) t) v where XH = 1) b,
(select distinct s.heatno,
sum(s.jmwgt) over(partition by s.heatno order by s.heatno) jmwgt,
s.materieltype,
substr(s.stationcode, 2, 2) code
from stl_additives s
where s.materieltype = 'HJ'
and substr(s.stationcode, 1, 1) = 'C'
and jmdate >= trunc(to_date('{2}','yyyy-mm-dd'))
and jmdate < trunc(to_date('{3}','yyyy-mm-dd')) + 1
union
select distinct s.heatno,
sum(s.jmwgt) over(partition by s.heatno order by s.heatno) jmwgt,
s.materieltype,
substr(s.stationcode, 2, 2) code
from j#stl_additives s
where s.materieltype = 'HJ'
and jmdate >= trunc(to_date('{2}','yyyy-mm-dd'))
and jmdate < trunc(to_date('{3}','yyyy-mm-dd')) + 1
and substr(s.stationcode, 1, 1) = 'C') h
where b.heatno = h.heatno(+) order by b.heatno";
sqlStr = Core.Mes.Client.Common.lgCommon.stringFormat(sqlStr, sqlConditionList);
return sqlStr;
}
}
}