Triton_160/Triton160RT/Module/DataLogManager.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using Aitex.Core.RT.DBCore;
using Aitex.Core.RT.Event;
using Aitex.Core.RT.Log;
using Aitex.Core.UI.ControlDataContext;
using Aitex.Core.Util;
using Aitex.Triton160.Common;
using Aitex.Triton160.RT.Routine.Process;

namespace Aitex.Triton160.RT.Module
{
    class DataLogManager : Singleton<DataLogManager>
    {
        /*
         * CREATE TABLE public."RecipeRunHistory"
(
  "RecipeRunGuid" text NOT NULL,
  "SusceptorId" text,
  "ProcessBeginTime" timestamp without time zone,
  "ProcessEndTime" timestamp without time zone,
  "ProcessIn" text,
  "RecipeName" text,
  "SusceptorStatus" text,
  "UserDefinedId" text,
  "Description" text,
  "WafersOnSusceptor" text,
  CONSTRAINT "RecipeRunHistory_pkey" PRIMARY KEY ("RecipeRunGuid")
)
         * 
         */
        public List<string> GetHistoryRecipeList(DateTime begin, DateTime end)
        {
            List<string> result = new List<string>();

            string sql = string.Format("SELECT * FROM \"RecipeRunHistory\" where \"ProcessBeginTime\" >= '{0}' and \"ProcessBeginTime\" <= '{1}' order by \"ProcessBeginTime\" ASC;",
    begin.ToString("yyyy/MM/dd HH:mm:ss.fff"), end.ToString("yyyy/MM/dd HH:mm:ss.fff"));

            DataSet ds = DB.ExecuteDataset(sql);
            if (ds == null)
                return result;

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string recipe = ds.Tables[0].Rows[i]["RecipeName"].ToString();
                if (!result.Contains(recipe))
                    result.Add(recipe);

            }

            ds.Clear();

            return result;
        }

        
        public List<DataLogItem> GetHistoryDataLogList(DateTime from, DateTime to, string recipeName, string lotName)
        {
            List<DataLogItem> result = new List<DataLogItem>();

            try
            {
                string sql =
                    string.Format(
                        "SELECT * FROM \"RecipeRunHistory\" where \"ProcessBeginTime\" >= '{0}' and \"ProcessBeginTime\" <= '{1}' {2} {3} order by \"ProcessBeginTime\" ASC;",
                        from.ToString("yyyy/MM/dd HH:mm:ss.fff"), to.ToString("yyyy/MM/dd HH:mm:ss.fff"),
                        string.IsNullOrEmpty(recipeName) ? "" : string.Format(" and \"RecipeName\" = '{0}'", recipeName),
                        string.IsNullOrEmpty(lotName) ? "" : string.Format(" and position('{0}' in \"SusceptorId\") >0" , lotName));

                DataSet ds = DB.ExecuteDataset(sql);
                if (ds == null)
                    return result;
 
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    result.Add(new DataLogItem()
                               {
                                   RecipeRunGuid =
                                       ds.Tables[0].Rows[i]["RecipeRunGuid"] is DBNull
                                           ? ""
                                           : ds.Tables[0].Rows[i]["RecipeRunGuid"].ToString(),

                                   ProcessBeginTime =
                                       ds.Tables[0].Rows[i]["ProcessBeginTime"] is DBNull
                                           ? ""
                                           : ds.Tables[0].Rows[i]["ProcessBeginTime"].ToString(),

                                   ProcessEndTime =
                                       ds.Tables[0].Rows[i]["ProcessEndTime"] is DBNull
                                           ? ""
                                           : ds.Tables[0].Rows[i]["ProcessEndTime"].ToString(),

                                   RecipeName =
                                       ds.Tables[0].Rows[i]["RecipeName"] is DBNull
                                           ? ""
                                           : ds.Tables[0].Rows[i]["RecipeName"].ToString(),

                                   LotName =
                                       ds.Tables[0].Rows[i]["SusceptorId" +
                                                            ""] is DBNull
                                           ? ""
                                           : ds.Tables[0].Rows[i]["SusceptorId"].ToString(),

                                   DataLogName = 
                                       ds.Tables[0].Rows[i]["UserDefinedId"] is DBNull
                                           ? ""
                                           : ds.Tables[0].Rows[i]["UserDefinedId"].ToString(),

                                   RecipeResult =
                                       ds.Tables[0].Rows[i]["SusceptorStatus"] is DBNull
                                           ? ""
                                           : ds.Tables[0].Rows[i]["SusceptorStatus"].ToString()
                               });

                }

                ds.Clear();
            }catch( Exception ex)
            {
                LOG.Write(ex);
            }
            return result;
        }

        public List<HistoryDataItem> GetHistoryData(IEnumerable<string> keys, string recipeRunGuid)
        {
            List<HistoryDataItem> result = new List<HistoryDataItem>();

            try
            {

                string sql = string.Format("SELECT * FROM \"RecipeRunHistory\" where \"RecipeRunGuid\" = '{0}'",
                    recipeRunGuid);

                DataSet ds = DB.ExecuteDataset(sql);
                if (ds == null)
                    return result;

                if (ds.Tables[0].Rows.Count == 0)
                    return result;

                object from = ds.Tables[0].Rows[0]["ProcessBeginTime"];

                if (from is DBNull)
                {
                    LOG.Write(string.Format("{0} not set start time", recipeRunGuid));
                    return result;
                }

                DateTime begin = (DateTime) from;

                object to = ds.Tables[0].Rows[0]["ProcessEndTime"];
                if (to is DBNull)
                {
                    to = new DateTime(begin.Year, begin.Month, begin.Day, 23, 59, 59, 999);
                }

                DateTime end = (DateTime) to;

                sql = "select time AS InternalTimeStamp";
                foreach (var dataId in keys)
                {
                    sql += "," + string.Format("\"{0}\"", dataId);
                }
                sql += string.Format(" from \"{0}\" where time > {1} and time <= {2} order by time asc LIMIT 2000;",
                    begin.ToString("yyyyMMdd") + "." + ModuleName.System, begin.Ticks, end.Ticks);

                DataSet dataSet = DB.ExecuteDataset(sql);
                if (dataSet == null)
                    return result;

                if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0)
                    return result;
                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++)
                    {
                        HistoryDataItem data = new HistoryDataItem();
                        if (i == 0)
                        {
                            long ticks = (long) row[i];
                            dt = new DateTime(ticks);
                            continue;
                        }
                        else
                        {
                            string dataId = colName[i];
                            if (row[i] is DBNull || row[i] == null)
                            {
                                data.dateTime = dt;
                                data.dbName = colName[i];
                                data.value = 0;
                            }
                            else if (row[i] is bool)
                            {
                                data.dateTime = dt;
                                data.dbName = colName[i];
                                data.value = (bool) row[i] ? 1 : 0;
                            }
                            else
                            {
                                data.dateTime = dt;
                                data.dbName = colName[i];
                                data.value = float.Parse(row[i].ToString());
                            }
                        }
                        result.Add(data);
                    }
                }
                dataSet.Clear();
            }catch(Exception ex)
            {
                LOG.Write(ex);
            }

            return result;
        }
    }
}