XinYu_LG2MES/ClientSolution/Core.LgMes.Client.lgServiceMgt/LgChemeLementService/.svn/text-base/LgChemeLementService.cs.svn-base

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;
using Core.LgMes.Client.lgDbMgt;

namespace Core.LgMes.Client.lgServiceMgt.LgChemeLementService
{
    public class LgChemeLementService : Core.LgMes.Client.lgServiceMgt.BaseService.LgBaseService
    {

        private const string SQL_STR_CHEME = "select * from stl_chemelement  where 1 = 1 {0}   union all  select * from j#stl_chemelement   where 1 = 1 {0} ";
        private const string SQL_STR_CHEME1 = "select {1} from stl_chemelement  where 1 = 1 {0}   union all  select {1} from j#stl_chemelement   where 1 = 1 {0} ";
        private const string SQL_STR_CHEME02 = "select {0} from  dual ";
        private const string SQL_STR_CHEMCEQ = "select a.heatno,a.assaydate,a.chem_cd,a.chem_value,a.create_date from stl_chemelement_ceq a where 1 = 1 {0}";
        
        /// <summary>
        /// 按试样号获取成分实绩
        /// </summary>
        /// <param name="chargeNo"></param>
        /// <returns></returns>
        public DataSet ReturnDsOfChemeByCheckNo(ClientParamWithSqlConditionAndOpenBase param)
        {
            string sqlStr = string.Format(SQL_STR_CHEME, string.Format("and checkno = '{0}'",param.sqlCondition));
            DataSet ds = new DataSet();
            try
            {
                
               ds =  base.GetEjDataSet(new DbClientParam(sqlStr,param.ob));
            }
            catch
            { 
            }
            return ds;
        }

        /// <summary>
        /// 按炉号获取成分信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataSet ReturnDsOfChemeByHeatNo(ClientParamWithSqlConditionAndOpenBase param)
        {
            string sqlStr = string.Format(SQL_STR_CHEME, string.Format("and heatno = '{0}'", param.sqlCondition));
            sqlStr = string.Format(sqlStr, param.sqlCondition);
            DataSet ds = new DataSet();
            try
            {
               ds =  base.dbCommond.ExecuteReader(new DbClientParam(sqlStr, param.ob));
            }
            catch
            {
            }
            return ds;
        }

        /// <summary>
        /// 通过化验时间获取成分数据
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataSet ReturnDsOfChemeByAssayDate(ClientParamWithSqlConditionAndOpenBase param)
        {
            string sqlStr = string.Format(SQL_STR_CHEME, string.Format("and assaydate >=  {0}  and assaydate < {1} ", param.param[0].ToString(),param.param[1].ToString()));
            sqlStr = string.Format(sqlStr, param.sqlCondition);
            DataSet ds = new DataSet();
            try
            {
                ds = base.dbCommond.ExecuteReader(new DbClientParam(sqlStr, param.ob));
            }
            catch
            {
            }
            return ds;
        }

        /// <summary>
        /// 获取复合成分信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataSet ReturnDsOfChemeCeq(ClientParamWithSqlConditionAndOpenBase param)
        {
            string sqlStr = string.Format(SQL_STR_CHEMCEQ, param.sqlCondition);
         
            DataSet ds = new DataSet();
            try
            {
                ds = base.dbCommond.ExecuteReader(new DbClientParam(sqlStr, param.ob));
            }
            catch
            {
            }
            return ds;
        }




        public string ReturnChemCeqByMathRound(ClientParamWithSqlConditionAndOpenBase param)
        {
            DataSet ds = new DataSet();
            string sqlStr = SQL_STR_CHEME1;
            sqlStr = string.Format(sqlStr, param.param[0].ToString(),param.param[1].ToString());
            try
            {
                ds = base.dbCommond.ExecuteReader(new DbClientParam(sqlStr, param.ob));
            }
            catch
            {
            }

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }


        public string ReturnChemCeqByMathRoundWithRealData(ClientParamWithSqlConditionAndOpenBase param)
        {
            DataSet ds = new DataSet();
            string sqlStr = SQL_STR_CHEME02;
            sqlStr = string.Format(sqlStr, param.param[0].ToString());
            try
            {
                ds = base.dbCommond.ExecuteReader(new DbClientParam(sqlStr, param.ob));
            }
            catch
            {
            }

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            return "";
        }


        /// <summary>
        /// 每天计算复合元素，并写入到表stl_chemelement_ceq
        /// </summary>
        /// <param name="param"></param>
        public void StlChemelementCeq_Insert(ClientParamWithSqlConditionAndOpenBase param)
        {
            string sqlStr = @" insert into stl_chemelement_ceq(heatno ,assaydate ,chem_cd,chem_value,create_date)
            select heatno,to_char(a.assaydate,'yyyy-mm-dd HH24:mi:ss'),'{0}',{1},to_char(sysdate,'yyyymmddhh24miss' ) from stl_chemelement a where  1 = 1
            and steelname = '{2}' and a.assaydate >= trunc(sysdate ) - 2
            and not exists (select 1 from  stl_chemelement_ceq b where 1 = 1 and b.chem_cd = '{0}'  and a.heatno = b.heatno and a.assaydate = to_date(b.assaydate,'yyyy-mm-dd HH24:mi:ss')
            )
            ";

            string sqlStrhis = @" insert into stl_chemelement_ceq(heatno ,assaydate ,chem_cd,chem_value,create_date)
            select heatno,to_char(a.assaydate,'yyyy-mm-dd HH24:mi:ss'),'{0}',{1},to_char(sysdate,'yyyymmddhh24miss' ) from j#stl_chemelement a where  1 = 1
            and steelname = '{2}' and a.assaydate >= trunc(sysdate ) - 2
            and not exists (select 1 from  stl_chemelement_ceq b where 1 = 1 and b.chem_cd = '{0}'  and a.heatno = b.heatno and a.assaydate = to_date(b.assaydate,'yyyy-mm-dd HH24:mi:ss')
            )
            ";

            try
            {
                sqlStr = string.Format(sqlStr,param.param[0].ToString(), param.param[1].ToString(),param.param[2].ToString());
                base.dbCommond.ExecuteNonQuery(new DbClientParam(sqlStr,param.ob));
            }
            catch
            { 
            }

            try
            {
                sqlStrhis = string.Format(sqlStrhis, param.param[0].ToString(), param.param[1].ToString(), param.param[2].ToString());
                base.dbCommond.ExecuteNonQuery(new DbClientParam(sqlStrhis, param.ob));
            }
            catch
            { }

            
        }


        public void StlChemelementCeqByAssaydate_Insert(ClientParamWithSqlConditionAndOpenBase param)
        {
            string sqlStr = @" insert into stl_chemelement_ceq(heatno ,assaydate ,chem_cd,chem_value,create_date)
            select heatno,to_char(a.assaydate,'yyyy-mm-dd HH24:mi:ss'),'{0}',{1},to_char(sysdate,'yyyymmddhh24miss' ) from stl_chemelement a where  1 = 1
            and steelname = '{2}'  {3}
            and not exists (select 1 from  stl_chemelement_ceq b where 1 = 1 and b.chem_cd = '{0}'  and a.heatno = b.heatno and a.assaydate = to_date(b.assaydate,'yyyy-mm-dd HH24:mi:ss')
            )
            ";

            string sqlStrhis = @" insert into stl_chemelement_ceq(heatno ,assaydate ,chem_cd,chem_value,create_date)
            select heatno,to_char(a.assaydate,'yyyy-mm-dd HH24:mi:ss'),'{0}',{1},to_char(sysdate,'yyyymmddhh24miss' ) from j#stl_chemelement a where  1 = 1
            and steelname = '{2}'  {3}
            and not exists (select 1 from  stl_chemelement_ceq b where 1 = 1 and b.chem_cd = '{0}'  and a.heatno = b.heatno and a.assaydate = to_date(b.assaydate,'yyyy-mm-dd HH24:mi:ss')
            )
            ";

            try
            {
                sqlStr = string.Format(sqlStr, param.param[0].ToString(), param.param[1].ToString(), param.param[2].ToString(), param.param[3].ToString());
                base.dbCommond.ExecuteNonQuery(new DbClientParam(sqlStr, param.ob));
            }
            catch
            {
            }

            try
            {
                sqlStrhis = string.Format(sqlStrhis, param.param[0].ToString(), param.param[1].ToString(), param.param[2].ToString(), param.param[3].ToString());
                base.dbCommond.ExecuteNonQuery(new DbClientParam(sqlStrhis, param.ob));
            }
            catch
            { }


        }
    }
}