Triton_160/Common/DataAnalysisControl/Charting/ViewModel/ChartingBaseViewModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using System.Data;


using Aitex.UI.Charting.Model;
using Abt.Controls.SciChart;
using System.Windows.Media;
using System.Threading;
using System.Text.RegularExpressions;
using DataAnalysisControl.Core;
using Aitex.DataAnalysis.Core;


namespace Aitex.UI.Charting.ViewModel
{
    public class ChartingBaseViewModel : BaseViewModel
    {
        #region put protected database operations here
        private static object _dbLocker = new object();
        private static NpgsqlConnection _conn = null;

        public static string ServerName
        {
            get
            {
                string ip = RegEdit.ReadRegedit("Aitex", "Database_IP");
                if (string.IsNullOrEmpty(ip))
                {
                    //创建默认注册表
                    RegEdit.WriteRegedit("Aitex", "Database_Ip", "127.0.0.1");
                    RegEdit.WriteRegedit("Aitex", "Database_Port", "5432");
                    RegEdit.WriteRegedit("Aitex", "Database_User", "postgres");
                    RegEdit.WriteRegedit("Aitex", "Database_Pwd", "123456");
                    RegEdit.WriteRegedit("Aitex", "Database_Name", "postgres");
                }
                ip = RegEdit.ReadRegedit("Aitex", "Database_IP");
                string port = RegEdit.ReadRegedit("Aitex", "Database_Port");
                string user = RegEdit.ReadRegedit("Aitex", "Database_User");
                string pwd = RegEdit.ReadRegedit("Aitex", "Database_Pwd");
                string db = RegEdit.ReadRegedit("Aitex", "Database_Name");
                return String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};Enlist=true;CommandTimeout=10;Timeout=10;", ip, port, user, pwd, db);

                //return CONTEXT.GetDBConnString();
            }
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="reason"></param>
        /// <returns>True:创建成功   False：创建失败</returns>
        protected static bool CreateConnection(out string reason)
        {
            lock (_dbLocker)
            {
                CONTEXT.WriteLog("Charting工具开始创建Charting工具的数据库连接");
                reason = string.Empty;
                if (_conn != null)
                {
                    _conn.Close();
                    _conn = null;
                }

                string connectionString = ServerName;
                if (string.IsNullOrWhiteSpace(connectionString))
                {
                    throw new ArgumentNullException("ConnectionString");
                }

                try
                {
                    var conn = new NpgsqlConnection(connectionString);
                    conn.Open();
                    _conn = conn;
                }
                catch (Exception ex)
                {
                    reason = ex.Message;
                    CONTEXT.WriteLog(ex, string.Format("Charting工具创建PostgreSQL数据库连接失败，连接字段: 【{0}】", connectionString));
                    return false;
                }
                return true;
            }
        }

        /// <summary>
        /// 执行无需返回的sql语句
        /// </summary>
        /// <param name="cmdText"></param>
        public static void ExecuteNonQuery(string cmdText)
        {
            string reason;
            if (_conn == null && !CreateConnection(out reason))
                return;
            lock (_dbLocker)
            {
                if (_conn.State != ConnectionState.Open)
                    _conn.Open();
                using (NpgsqlCommand command = new NpgsqlCommand(cmdText, _conn))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string cmdText, params object[] p)
        {
            DeviceTimer tm = new DeviceTimer();
            tm.Start(0);
            string reason;
            if (_conn == null && !CreateConnection(out reason))
                return null;
            DataSet ds = new DataSet();
            lock (_dbLocker)
            {
                using (NpgsqlCommand command = new NpgsqlCommand())
                {
                    try
                    {
                        if (_conn.State != ConnectionState.Open)
                            _conn.Open();
                        command.Parameters.Clear();
                        command.Connection = _conn;
                        command.CommandText = cmdText;
                        command.CommandType = CommandType.Text;
               
                        if (p != null)
                        {
                            foreach (object parm in p)
                                command.Parameters.AddWithValue(string.Empty, parm);
                        }
                        using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(command))
                        {
                            da.Fill(ds);
                        }
                    }
                    catch (Exception ex)
                    {
                        //如果是Table不存在，则跳过该天的数据查询
                        if (ex is Npgsql.NpgsqlException &&
                            0 == String.Compare((ex as Npgsql.NpgsqlException).Code, "42P01"/*未定义的表（UNDEFINED TABLE）*/, true))
                        {
                            string errMsg = string.Format("执行SQL：【{0}】 发生Table不存在", cmdText);
                            CONTEXT.WriteLog(errMsg);
                        }
                        else
                        {
                            CreateConnection(out reason);
                            string errMsg = string.Format("执行SQL：【{0}】 发生异常，重新创建数据库连接", cmdText);
                            CONTEXT.WriteLog(ex, errMsg);
                            //throw new Exception(errMsg);
                        }
                    }
                }
            }
      
            return ds;
        }
        #endregion

        #region get recipe information
        /// <summary>
        /// 获取指定石墨盘的工艺程序运行每一步的时间信息+步骤名称
        /// </summary>
        /// <param name="susceptorId"></param>
        /// <returns></returns>
        public List<RecipeSyncPoint> GetRecipeStepInfo(string recipeRunGuid)
        {
            var Ret = new List<RecipeSyncPoint>();
            try
            {
                string sql = string.Format("SELECT * FROM \"SusceptorData\" where \"SusceptorId\"= '{0}' and \"ItemType\" = '{1}' order by \"Time\" asc;",
                    recipeRunGuid, "RecipeStepStart");
                Ret.Add(new RecipeSyncPoint() { StepTime = DateTime.MinValue, StepName = "NULL" });
                var ds = ExecuteDataset(sql);
                if (ds != null && ds.Tables != null && ds.Tables.Count >= 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        var row = ds.Tables[0].Rows[i];
                        var stepTime = Convert.ToDateTime(row["Time"].ToString());
                        var stepDesc = row["ItemRemark"].ToString();
                        if (stepDesc.StartsWith("工艺运行中：开始"))
                            stepDesc = stepDesc.Replace("工艺运行中：开始", string.Empty);
                        Ret.Add(new RecipeSyncPoint() { StepTime = stepTime, StepName = stepDesc });
                    }
                }
            }
            catch (Exception ex)
            {
                CONTEXT.WriteLog(ex, string.Format("获取工艺Run编号{0}的工艺处理数据发生异常", recipeRunGuid));
            }
            return Ret;
        }
        #endregion

        #region get susceptor list

        /// <summary>
        /// 石墨盘数据模型
        /// </summary>
        public class SusceptorDataModel
        {
            public string RecipeRunGuid { get; set; }
            public string SusceptorId { get; set; }
            public string UserDefinedId { get; set; }
            public string RecipeName { get; set; }
            /// <summary>
            /// 仅用于界面显示，em. 20131219131415_LED001-(2)    简化为 LED001-(2)
            /// </summary>
            public string RecipeShortName
            {
                get
                {
                    if (!string.IsNullOrEmpty(RecipeName) && Regex.IsMatch(RecipeName, @"^\d\d\d\d\d\d\d\d\d\d\d\d\d\d_*") && RecipeName.Length > 15)
                        return RecipeName.Substring(15);
                    return RecipeName;
                }
            }
            //public string CreateTime { get; set; }
            //public string DeleteTime { get; set; }
            public string ProcessBeginTime { get; set; }
            public string ProcessEndTime { get; set; }
            public string ProcessIn { get; set; }
            public string SusceptorStatus { get; set; }
            public string WaferOnSusceptor { get; set; }
            public string Description { get; set; }
        }

        /// <summary>
        /// get susceptor list
        /// </summary>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <param name="processIn"></param>
        /// <returns></returns>
        public List<SusceptorDataModel> GetSusceptorList(DateTime begin, DateTime end, string processIn)
        {
            return GetSusceptorList(begin, end, processIn, "", "", "");
        }

        /// <summary>
        /// get susceptor list
        /// </summary>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <param name="processIn"></param>
        /// <param name="recipeName"></param>
        /// <param name="description"></param>
        /// <param name="userDefinedId"></param>
        /// <returns></returns>
        public List<SusceptorDataModel> GetSusceptorList(DateTime begin, DateTime end, string processIn, string recipeName, string description, string userDefinedId)
        {
            var ret = new List<SusceptorDataModel>();
            try
            {
                string sql = string.Format("SELECT * FROM \"RecipeRunHistory\" where \"ProcessBeginTime\" >= '{0}' and \"ProcessBeginTime\" <= '{1}' {2} {3} {4} {5} order by \"ProcessBeginTime\" asc;",

                    begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"),
                    string.IsNullOrEmpty(processIn) ? "" : string.Format(" and \"ProcessIn\" = '{0}'", processIn),
                    string.IsNullOrEmpty(recipeName) ? "" : string.Format(" and \"RecipeName\" = '{0}'", recipeName),
                    string.IsNullOrEmpty(description) ? "" : string.Format(" and \"Description\" = '{0}'", description),
                    string.IsNullOrEmpty(userDefinedId) ? "" : string.Format(" and \"UserDefinedId\" = '{0}'", userDefinedId));
                using (var dataSet = ExecuteDataset(sql))
                {
                    for(int i = 0; i < dataSet.Tables[0].Rows.Count;i++)
                    {
                        var row = dataSet.Tables[0].Rows[i];
                        string susceptorStatusString = row["SusceptorStatus"].ToString();
                        //SusceptorStatus eSt;
                        //bool isSucc = Enum.TryParse<SusceptorStatus>(susceptorStatusString, out eSt);
                        //if (isSucc) susceptorStatusString = Common.Utilities.Converter.SusceptorStatus(eSt);
                        ret.Add(new SusceptorDataModel()
                        {
                            RecipeRunGuid = row["RecipeRunGuid"].ToString(),
                            //CreateTime = row["CreateTime"].ToString(),
                            //DeleteTime = row["DeleteTime"].ToString(),
                            Description = row["Description"].ToString(),
                            ProcessBeginTime = row["ProcessBeginTime"].ToString(),
                            ProcessEndTime = row["ProcessEndTime"].ToString(),
                            ProcessIn = row["ProcessIn"].ToString(),
                            RecipeName = row["RecipeName"].ToString(),
                            SusceptorId = row["SusceptorId"].ToString(),
                            UserDefinedId = row["UserDefinedId"].ToString(),
                            SusceptorStatus = susceptorStatusString,
                            WaferOnSusceptor = row["WafersOnSusceptor"].ToString()
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                CONTEXT.WriteLog(ex, string.Format("获取{0}~{1}期间，{2}处理，工艺程序为{3}的石墨盘记录发生错误", begin, end, processIn, recipeName));
            }
            return ret;
        }
        #endregion

        #region get equipment data from database
        /// <summary>
        /// get data from PostgreSQL database
        /// </summary>
        /// <param name="chamId"></param>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <param name="dataIdList"></param>
        /// <param name="returnDatas"></param>
        /// <returns></returns>
        public bool GetDbData(string chamId, DateTime from, DateTime to, IEnumerable<string> dataIdList, out Dictionary<string, DataItem> returnDatas)
        {
            returnDatas = new Dictionary<string, DataItem>();
            for (DateTime dfrom = new DateTime(from.Year, from.Month, from.Day);
                dfrom < to; dfrom += new TimeSpan(1, 0, 0, 0))
            {
                DateTime begin = (dfrom.Year == from.Year && dfrom.Month == from.Month && dfrom.Day == from.Day) ? from : new DateTime(dfrom.Year, dfrom.Month, dfrom.Day, 0, 0, 0, 0);
                DateTime end = (dfrom.Date == to.Date) ? to : new DateTime(dfrom.Year, dfrom.Month, dfrom.Day, 23, 59, 59, 999);
                //if (begin.Date > DateTime.Today)
                //    continue;
                try
                {
                    string sql = "select time AS InternalTimeStamp";
                    foreach (var dataId in dataIdList)
                    {
                        if (!returnDatas.Keys.Contains(dataId) && !dataId.Contains("PM.VirtualDevice"))
                        {
                            returnDatas.Add(dataId, new DataItem() { DataName = dataId, RawData = new List<float>(), TimeStamp = new List<DateTime>() });
                            sql += "," + string.Format("\"{0}\"", dataId);
                        }
                    }

                    sql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc;",
                        begin.ToString("yyyyMMdd") + "." + chamId.ToString(), begin.Ticks, end.Ticks);

                    using (var dataSet = ExecuteDataset(sql))
                    {
                        if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
                            continue;
                        DateTime dt = new DateTime();
                        Dictionary<int, string> colName = new Dictionary<int, string>();
                        for (int colNo = 0; colNo < dataSet.Tables[0].Columns.Count; colNo++)
                            colName.Add(colNo, dataSet.Tables[0].Columns[colNo].ColumnName);
                        for(int rowNo = 0 ; rowNo < dataSet.Tables[0].Rows.Count; rowNo++)
                        {
                            var row = dataSet.Tables[0].Rows[rowNo];

                            for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
                            {
                                if (i == 0)
                                {
                                    long ticks = (long)row[i];
                                    dt = new DateTime(ticks);
                                }
                                else
                                {
                                    string dataId = colName[i];
                                    if (row[i] is DBNull || row[i] == null)
                                    {
                                        returnDatas[dataId].RawData.Add(0);// ds.Data[].Add(new KeyValuePair<DateTime,float>(dt, 0));
                                        returnDatas[dataId].TimeStamp.Add(dt);
                                    }
                                    else if (row[i] is bool)
                                    {
                                        //ds.Data[reader.GetName(i)].Add(new KeyValuePair<DateTime, float>(dt, (bool)reader[i] ? 1 : 0));
                                        returnDatas[dataId].RawData.Add((bool)row[i] ? 1 : 0);
                                        returnDatas[dataId].TimeStamp.Add(dt);
                                    }
                                    else
                                    {
                                        //ds.Data[reader.GetName(i)].Add(new KeyValuePair<DateTime, float>(dt, float.Parse(reader[i].ToString())));
                                        returnDatas[dataId].RawData.Add(float.Parse(row[i].ToString()));
                                        returnDatas[dataId].TimeStamp.Add(dt);
                                    }
                                }
                            }
                        }
                        dataSet.Clear();
                        GC.Collect();
                    }
                }
                catch (Exception ex)
                {
                    CONTEXT.WriteLog(ex);
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// Read data name list from database
        /// </summary>
        /// <param name="beginTime"></param>
        /// <param name="whichCham"></param>
        /// <param name="reason"></param>
        /// <returns></returns>
        public void ReadDbDataNameList(DateTime beginTime, string whichCham, out string reason, out List<string> dataList, out Dictionary<string, Tuple<string, List<string>>> virtualDeviceList)
        {
            dataList = new List<string>();
            virtualDeviceList = new Dictionary<string, Tuple<string, List<string>>>();
            reason = string.Empty;
            try
            {
                string tblName = beginTime.ToString("yyyyMMdd.") + whichCham;
                //query if table already exist?
                string sqlTblDefine = string.Format("select cols.column_name, (select pg_catalog.obj_description(oid) from pg_catalog.pg_class c where c.relname=cols.table_name) as table_comment ,(select pg_catalog.col_description(oid,cols.ordinal_position::int) from pg_catalog.pg_class c where c.relname=cols.table_name) as column_comment from information_schema.columns cols where cols.table_name='{0}';", tblName);
                using (var dataSet = ExecuteDataset(sqlTblDefine))
                {
                    for(int i= 0; i < dataSet.Tables[0].Rows.Count;i++)
                        dataList.Add(dataSet.Tables[0].Rows[i]["column_name"].ToString());
                }
                //if table exists, add virtual device datas
                if (dataList.Count > 0)
                {
                    string sqlVirtualTblDefine = string.Format("SELECT \"DeviceName\", \"FunctionBlock\" FROM \"VirtualDevice\" where \"ChamberID\" = '{0}';", whichCham);
                    bool isAny = false;
                    using (var dataSet = ExecuteDataset(sqlVirtualTblDefine))
                    {
                        for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
                        {
                            var virtualDeviceName = "PM.VirtualDevice." + dataSet.Tables[0].Rows[i]["DeviceName"].ToString();
                            var functionBlock = dataSet.Tables[0].Rows[i]["FunctionBlock"].ToString();
                            var varList = new List<string>();
                            var collection = Regex.Matches(functionBlock, @"PM(\.\w+)+");
                            foreach(var item in collection)
                            {
                                varList.Add(item.ToString());
                            }
                            isAny = true;
                            dataList.Add(virtualDeviceName);
                            virtualDeviceList.Add(virtualDeviceName, new Tuple<string, List<string>>(functionBlock, varList));
                        }
                    }
                    if (!isAny)
                    {
                        dataList.Add("PM.VirtualDevice");
                    }
                }
            }
            catch (Exception ex)
            {
                reason = ex.Message;
                CONTEXT.WriteLog(ex, string.Format("获取{0} {1}数据库变量名列表发生异常", whichCham, beginTime.ToString("yyyy/MM/dd HH:mm:ss")));
            }
        }


        #region 获取wafer显示顺序
        /// <summary>
        /// 获取wafer显示顺序
        /// </summary>
        /// <returns></returns>
        public string GetWaferDisplayIndex(DateTime startTime, string ProcessIn)
        {
            string result = string.Empty;

            try
            {
                string sql = string.Format("SELECT \"DisplayIndex\" FROM \"WaferDisplayIndex\" WHERE \"Id\" =( SELECT \"RecipeRunGuid\" FROM \"RecipeRunHistory\" where to_char(\"ProcessBeginTime\",'yyyy-MM-DD HH24:MI:SS') = '{0}' and \"ProcessIn\"='{1}');",
                        startTime.ToString("yyyy-MM-dd HH:mm:ss"), ProcessIn);

                using (var dataSet = ExecuteDataset(sql))
                {
                    if (dataSet.Tables[0].Rows.Count > 0)
                    { result = dataSet.Tables[0].Rows[0]["DisplayIndex"].ToString(); }
                }
            }
            catch (System.Exception ex)
            {
                CONTEXT.WriteLog(ex, "数据库读取wafer排序信息失败");
                //result = "60,61,62,63,64,65,66,47,48,49,50,51,52,53,54,55,56,57,58,59,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26";
            }
            return result;
        }

        /// <summary>
        /// 获取wafer映射表
        /// </summary>
        public string WaferDisplayIndex { get; set; }

        #endregion
        #endregion
    }
}