XinYu_LG2MES/ClientSolution/Core.LgMes.Client.lgServiceMgt/LgSqlCollection/LgReportSql/SqlCollectionCcm.cs

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
{
    /// <summary>
    /// ccm sql 集合
    /// </summary>
    public class SqlCollectionCcm
    {
        /// <summary>
        /// 板坯铸机典拉表 20140325 ym
        /// </summary>
        /// <param name="sqlConditionList"></param>
        /// <returns></returns>
        public static string ReturnSqlOfCcmDlReport(ArrayList sqlConditionList)
        {
            string strSql = "select RQ \"日期\", BC \"班次\", BB \"班别\", HEATNO \"炉号\", PLANSTEEL \"钢种\", "
          + "BJZCX \"班浇注次序\", ZBCX \"中包次序\", trim(to_char(JZDW, '990.0')) \"浇注吨位\", SFYL \"是否引流\", "
          + "to_char(balestarttime, 'HH24:mi') \"大包开浇时间\", "
          + "to_char(baleendtime, 'HH24:mi') \"大包关闭时间\", "
          + " substr(stationcode,2,1) CCM_ID, "
          + "trim(to_char(round((case when cast_no = cast_no_next then balestarttime_next else baleendtime end - balestarttime)*24*60, 1), '990.0')) "
          + "\"浇注周期\", "
          + "temp_min \"最低_1\", temp_max \"最高_1\", wc \"温差\", "
          + "round(spd_min, 2) \"最低_2\", round(spd_max, 2) \"最高_2\", round(bd, 2) \"波动\", "
          + "S \"S\", Als \"Als\", N \"N\", MEMO \"备注\" ";

            strSql = strSql + @"   from (select to_char(case
			                         when a.BALESTARTTIME is not null then
			                          a.BALESTARTTIME
			                         else
			                          a.OPTDATE
			                       end,
			                       'yyyy-MM-dd') RQ,
			               case substr(a.SHIFTCODE, 1, 1)
			                 when '1' then
			                  '早'
			                 when '2' then
			                  '中'
			                 when '3' then
			                  '夜'
			               end BC,
			               case substr(a.SHIFTCODE, 2, 1)
			                 when '1' then
			                  '甲'
			                 when '2' then
			                  '乙'
			                 when '3' then
			                  '丙'
			                 when '4' then
			                  '丁'
			               end BB,
			               a.HEATNO,
			               a.STATIONCODE,
			               a.PLANSTEEL,
			               row_number() over(partition by to_char(case
			                 when a.BALESTARTTIME is not null then
			                  a.BALESTARTTIME
			                 else
			                  a.OPTDATE
			               end, 'yyyy-MM-dd'), a.SHIFTCODE order by a.BALESTARTTIME) BJZCX,
			               row_number() over(partition by b.CAST_NO order by a.BALESTARTTIME) ZBCX,
			               round(nvl(a.PFBALEWGT, 0) - nvl(a.BALELEAVEWGT, 0), 1) JZDW,
			               case nvl(a.BALEFLOWFLAG, 0)
			                 when 1 then
			                  '×'
			               end SFYL,
			               a.BALESTARTTIME,
			               a.BALEENDTIME,
			               nvl(b.CAST_NO, a.MOULDNO) CAST_NO,
			               lead(a.BALESTARTTIME, 1, a.BALEENDTIME) over(order by a.BALESTARTTIME)  BALESTARTTIME_NEXT,
			               lead(b.CAST_NO, 1, a.MOULDNO) over(order by a.BALESTARTTIME) CAST_NO_NEXT,
			               round(c.TEMP_MIN) TEMP_MIN,
			               round(c.TEMP_MAX) TEMP_MAX,
			               round((c.TEMP_MAX - c.TEMP_MIN)) WC,
			               c.SPD_MIN,
			               c.SPD_MAX,
			               (c.SPD_MAX - c.SPD_MIN) BD,
			               trim(to_char(d.S, '0.000')) S,
			               trim(to_char(d.Als, '0.000')) Als,
			               trim(to_char(d.N, '0.0000')) N,
			               a.MEMO
			          from (select *
			                  from STL_CCM_OPTINFO
			                 where  1 = 1 {0}
			                union all
			                select *
			                  from J#STL_CCM_OPTINFO
			                 where 1 = 1 {0}) a,
			               (select *
			                  from ppc_STEEL_HEAT
			                 where 1 = 1 {1}) b,
			               (select distinct x.HEATNO,
			                                min(case
			                                      when nvl(x.MIDLADLETEMP, 0) between {2} and {3} then
			                                       nvl(x.MIDLADLETEMP, 0)
			                                    end) over(partition by x.HEATNO) TEMP_MIN,
			                                max(case
			                                      when nvl(x.MIDLADLETEMP, 0) between {2} and {3} then
			                                       nvl(x.MIDLADLETEMP, 0)
			                                    end) over(partition by x.HEATNO) TEMP_MAX,
			                                min(LEAST(x.S1CASTSPEED,X.S2CASTSPEED)) over(partition by x.HEATNO) SPD_MIN,
			                                max(GREATEST(x.S1CASTSPEED,x.S2CASTSPEED)) over(partition by x.HEATNO)  SPD_MAX
			                  from (select *
			                          from  {4}
			                         where  1 = 1 {5}
			                        union all
			                        select *
			                          from J#{4}
			                         where 1 = 1 {5}) x) c,
			               (select y.*
			                  from (select x.*,
			                               row_number() over(partition by x.HEATNO order by x.ASSAYDATE) xh
			                          from (select *
			                                  from STL_CHEMELEMENT
			                                 where (upper(substr(ASSAYTYPECODE, 1, 2)) = 'CP' or
			                                       upper(substr(STATIONCODE, 1, 1)) = 'G')
			                                   {6}
			                                union all
			                                select *
			                                  from J#STL_CHEMELEMENT
			                                 where (upper(substr(ASSAYTYPECODE, 1, 2)) = 'CP' or
			                                       upper(substr(STATIONCODE, 1, 1)) = 'G')
			                                   {6}) x) y
			                 where y.xh = 1) d
			         where a.HEATNO = b.HEATNO(+)
			           and a.HEATNO = c.HEATNO(+)
			           and a.HEATNO = d.HEATNO(+)
			         order by a.BALESTARTTIME)
			 where RQ between {7} and {8}";

            strSql = lgCommon.stringFormat(strSql,sqlConditionList);

            return strSql;
        }

        /// <summary>
        /// 获取炉号的最小和最大给定拉速
        /// </summary>
        /// <param name="sqlWhere"></param>
        /// <returns></returns>
        public static string returnSqlForMinSpeedAndMaxSpeedForHeatNo(string sqlWhere)
        {
            string sqlstr = "";

            sqlstr = sqlstr + " select heatno,min(fixedspeed) min_fixedspeed,max(fixedspeed) maxfixedspeed from ( "
                            + " select a.heatno,a.fixedspeed from j#stl_ccm_fixedcastspeed a "
                            + " where 1 = 1  {0}  union all "
                            + " select a.heatno,a.fixedspeed from stl_ccm_fixedcastspeed a where 1 = 1 {0} ) group by heatno";

            sqlstr = string.Format(sqlstr, sqlWhere);


            return sqlstr;
        }

    }
}