ELK/FrameworkLocal/Common/DBCore/RecipeEditHistoryRecorder.cs

using Aitex.Core.RT.DBCore;
using Aitex.Core.RT.Log;
using Aitex.Core.RT.SCCore;
using MECF.Framework.Common.CommonData;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MECF.Framework.Common.DBCore
{
    public class RecipeEditHistoryRecorder
    {
        public static void SaveRecipeHistory(RecipeHistory recipeHistory)
        {
            try
            {
                string guid = Guid.NewGuid().ToString();
                string sql = string.Format("INSERT INTO \"Recipe_History\"(\"guid\", \"createdBy\", \"creationTime\",\"lastRevisedBy\", \"lastRevisionTime\", \"recipe_description\", \"recipe_type\", \"recipe_name\", \"recipe_path\", \"recipe_version\", \"recipe_level\", \"recipe_premission\", \"recipe_compare\", \"recipe_content\")VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}');",
                    guid,
                    recipeHistory.CreatedBy,
                    recipeHistory.CreationTime.ToString("yyyy/MM/dd HH:mm:ss.fff"),
                    recipeHistory.LastRevisedBy,
                    recipeHistory.LastRevisionTime.ToString("yyyy/MM/dd HH:mm:ss.fff"),
                    recipeHistory.Recipe_Description,
                    recipeHistory.Recipe_Type,
                    recipeHistory.Recipe_Name,
                    recipeHistory.Recipe_Path,
                    recipeHistory.Recipe_Version,
                    recipeHistory.Recipe_Level,
                    recipeHistory.Recipe_Premission,
                    recipeHistory.Recipe_Compare,
                    recipeHistory.Recipe_Content);
                DB.Insert(sql);
                var saveNumber = SC.GetValue<int>("System.Recipe.RecipeSaveNumber");
                ClearByRecipePathHistory(recipeHistory.Recipe_Path, saveNumber);
            }
            catch (Exception)
            {

                throw;
            }

        }

        public static List<RecipeHistory> QueryDBRecipeHistory(string sql)
        {
            List<RecipeHistory> result = new List<RecipeHistory>();
            try
            {
                DataSet ds = DB.ExecuteDataset(sql);
                if (ds == null)
                    return result;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    RecipeHistory ev = new RecipeHistory();
                    ev.Guid = ds.Tables[0].Rows[i]["guid"].ToString();
                    ev.CreatedBy = ds.Tables[0].Rows[i]["createdBy"].ToString();
                    if (!ds.Tables[0].Rows[i]["creationTime"].Equals(DBNull.Value))
                    { ev.CreationTime = ((DateTime)ds.Tables[0].Rows[i]["creationTime"]); }
                    ev.LastRevisedBy = ds.Tables[0].Rows[i]["lastRevisedBy"].ToString();
                    if (!ds.Tables[0].Rows[i]["lastRevisionTime"].Equals(DBNull.Value))
                    { ev.LastRevisionTime = ((DateTime)ds.Tables[0].Rows[i]["lastRevisionTime"]); }
                    ev.Recipe_Description = ds.Tables[0].Rows[i]["recipe_description"].ToString();
                    ev.Recipe_Type = ds.Tables[0].Rows[i]["recipe_type"].ToString();
                    ev.Recipe_Name = ds.Tables[0].Rows[i]["recipe_name"].ToString();
                    ev.Recipe_Path = ds.Tables[0].Rows[i]["recipe_path"].ToString();
                    ev.Recipe_Compare = ds.Tables[0].Rows[i]["recipe_compare"].ToString();
                    ev.Recipe_Content = ds.Tables[0].Rows[i]["recipe_content"].ToString();
                    result.Add(ev);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
            }
            return result;
        }

        private static List<RecipeHistory> QueryByRecipePath(string sql)
        {
            List<RecipeHistory> result = new List<RecipeHistory>();
            try
            {

                DataSet ds = DB.ExecuteDataset(sql);
                if (ds == null)
                    return result;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    RecipeHistory ev = new RecipeHistory();
                    ev.Guid = ds.Tables[0].Rows[i]["guid"].ToString();
                    ev.CreatedBy = ds.Tables[0].Rows[i]["createdBy"].ToString();
                    if (!ds.Tables[0].Rows[i]["creationTime"].Equals(DBNull.Value))
                    { ev.CreationTime = ((DateTime)ds.Tables[0].Rows[i]["creationTime"]); }
                    ev.LastRevisedBy = ds.Tables[0].Rows[i]["lastRevisedBy"].ToString();
                    if (!ds.Tables[0].Rows[i]["lastRevisionTime"].Equals(DBNull.Value))
                    { ev.LastRevisionTime = ((DateTime)ds.Tables[0].Rows[i]["lastRevisionTime"]); }
                    ev.Recipe_Description = ds.Tables[0].Rows[i]["recipe_description"].ToString();
                    ev.Recipe_Type = ds.Tables[0].Rows[i]["recipe_type"].ToString();
                    ev.Recipe_Name = ds.Tables[0].Rows[i]["recipe_name"].ToString();
                    ev.Recipe_Path = ds.Tables[0].Rows[i]["recipe_path"].ToString();
                    ev.Recipe_Level = ds.Tables[0].Rows[i]["recipe_level"].ToString();
                    ev.Recipe_Version = ds.Tables[0].Rows[i]["recipe_version"].ToString();
                    ev.Recipe_Premission = ds.Tables[0].Rows[i]["recipe_premission"].ToString();
                    ev.Recipe_Compare = ds.Tables[0].Rows[i]["recipe_compare"].ToString();
                    ev.Recipe_Content = ds.Tables[0].Rows[i]["recipe_content"].ToString();
                    result.Add(ev);
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
            }
            return result;
        }

        public static List<RecipeHistory> QueryByRecipePathHistory(string RecipePath, DateTime startTime, DateTime endTime, int row = 20)
        {
            string sql = string.Format("SELECT * FROM \"Recipe_History\" where \"recipe_path\" = '{0}'  limit {1};", RecipePath, row);
            return QueryByRecipePath(sql);
        }

        public static List<RecipeHistory> QueryByRecipePathHistory(string RecipePath, int row = 20)
        {
            string sql = string.Format("SELECT * FROM \"Recipe_History\" where \"recipe_path\" = '{0}'  limit {1};", RecipePath, row);
            return QueryByRecipePath(sql);
        }

        public static bool ClearByRecipePathHistory(string RecipePath)
        {

            return true;
        }

        public static bool ClearByRecipePathHistory(string RecipePath, DateTime startTime, DateTime endTime)
        {

            return true;
        }

        /// <summary>
        /// 根据RecipePath删除记录
        /// </summary>
        /// <param name="RecipePath">查询路径</param>
        /// <param name="saveNumber">删除后保留记录条数</param>
        /// <returns>删除是否成功</returns>
        public static bool ClearByRecipePathHistory(string RecipePath, int saveNumber)
        {
            try
            {
                List<RecipeHistory> result = new List<RecipeHistory>();
                string sql = string.Format("SELECT * FROM \"Recipe_History\" where \"recipe_path\" = '{0}' order by  \"creationTime\"  ASC;", RecipePath);
                DataSet ds = DB.ExecuteDataset(sql);
                if (ds == null)
                    return true;
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    RecipeHistory ev = new RecipeHistory();
                    ev.Guid = ds.Tables[0].Rows[i]["guid"].ToString();
                    ev.CreatedBy = ds.Tables[0].Rows[i]["createdBy"].ToString();
                    if (!ds.Tables[0].Rows[i]["creationTime"].Equals(DBNull.Value))
                    { ev.CreationTime = ((DateTime)ds.Tables[0].Rows[i]["creationTime"]); }
                    ev.LastRevisedBy = ds.Tables[0].Rows[i]["lastRevisedBy"].ToString();
                    if (!ds.Tables[0].Rows[i]["lastRevisionTime"].Equals(DBNull.Value))
                    { ev.LastRevisionTime = ((DateTime)ds.Tables[0].Rows[i]["lastRevisionTime"]); }
                    ev.Recipe_Description = ds.Tables[0].Rows[i]["recipe_description"].ToString();
                    ev.Recipe_Type = ds.Tables[0].Rows[i]["recipe_type"].ToString();
                    ev.Recipe_Name = ds.Tables[0].Rows[i]["recipe_name"].ToString();
                    ev.Recipe_Path = ds.Tables[0].Rows[i]["recipe_path"].ToString();
                    ev.Recipe_Level = ds.Tables[0].Rows[i]["recipe_level"].ToString();
                    ev.Recipe_Version = ds.Tables[0].Rows[i]["recipe_version"].ToString();
                    ev.Recipe_Premission = ds.Tables[0].Rows[i]["recipe_premission"].ToString();
                    ev.Recipe_Compare = ds.Tables[0].Rows[i]["recipe_compare"].ToString();
                    ev.Recipe_Content = ds.Tables[0].Rows[i]["recipe_content"].ToString();
                    result.Add(ev);
                }
                if (result.Count > saveNumber)
                {
                    for (int i = 0; i < result.Count - saveNumber; i++)
                    {
                        string sql1 = string.Format("Delete FROM \"Recipe_History\" where \"guid\" = '{0}'", result[i].Guid);
                        DB.ExecuteNonQuery(sql1);
                    }
                }
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
            }
            return true;
        }
    }


}