#region

using System;
using System.Data;
using System.Text;
using CSFramework.DB;
using CSFrameworkV5.Common;
using CSFrameworkV5.Core;
using CSFrameworkV5.Interfaces;
using CSFrameworkV5.Models;

#endregion

namespace CSFrameworkV5.DataAccess
{
    public class dalLog : dalBase, IBridge_Log
    {
        /// <summary>
        ///     构造器
        /// </summary>
        /// <param name="user">当前用户登录信息</param>
        public dalLog(Loginer user)
            : base(user)
        {
            _Database = DatabaseProvider.SystemDatabase;
        }

        public void ClearAll()
        {
            _Database.ExecuteSQL("DELETE FROM sys_LogOperation");
        }

        public void ClearLog(DateTime beginDate, DateTime endDate)
        {
            var sql =
                $"DELETE FROM sys_LogOperation WHERE OperationTime BETWEEN {_Database.ParamSymboName}D1 AND {_Database.ParamSymboName}D2";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("D1", DbType.DateTime, beginDate);
            cmd.AddParam("D2", DbType.DateTime, endDate);
            _Database.ExecuteCommand(cmd.Command);
        }

        public void ClearLog(string user, DateTime beginDate, DateTime endDate)
        {
            var sql =
                $"DELETE FROM sys_LogOperation WHERE Account={_Database.ParamSymboName}Account AND OperationTime BETWEEN {_Database.ParamSymboName}D1 AND {_Database.ParamSymboName}D2";
            var cmd = _Database.CreateCommand(sql);
            cmd.AddParam("Account", DbType.String, user);
            cmd.AddParam("D1", DbType.DateTime, beginDate);
            cmd.AddParam("D2", DbType.DateTime, endDate);
            _Database.ExecuteCommand(cmd.Command);
        }

        public int DeleteLoginLog(string isids)
        {
            var cmd = _Database.CreateSqlProc("usp_DeleteLoginLog");
            cmd.AddParam("isids", isids);
            var i = _Database.ExecuteCommand(cmd.Command);
            return i;
        }

        public int DeleteOperateLog(string isids)
        {
            var cmd = _Database.CreateSqlProc("usp_DeleteOperateLog");
            cmd.AddParam("isids", isids);
            var i = _Database.ExecuteCommand(cmd.Command);
            return i;
        }

        public DataSet GetDynamicReport(string[] years)
        {
            //组合SQL1
            var sb = new StringBuilder();
            sb.AppendLine("SELECT Account,OperationType, ");
            for (var i = 0; i <= years.Length - 1; i++)
            {
                var year = years[i].Trim(); //必须去空格!
                var item =
                    $"	SUM(CASE WHEN YEAR(OperationTime)={year} THEN 1 ELSE 0 END) AS Y{year} ";
                if (i < years.Length - 1) item += ", ";

                sb.AppendLine(item);
            }

            sb.AppendLine("FROM sys_LogOperation ");
            sb.AppendLine("GROUP BY Account,OperationType; ");
            var sql1 = sb.ToStringEx();

            var p = DatabaseProvider.SystemDatabase.ParamSymboName;

            //组合SQL2
            var sql2 = "SELECT YEAR(OperationTime) AS YearItem, " +
                       "   SUM(CASE WHEN OperationType = " + p +
                       "OperationType1 THEN 1 ELSE 0 END) AS OpenForm, " +
                       "   SUM(CASE WHEN OperationType = " + p +
                       "OperationType2 THEN 1 ELSE 0 END) AS Exception, " +
                       "   SUM(CASE WHEN OperationType = " + p +
                       "OperationType3 THEN 1 ELSE 0 END) AS CustomError " +
                       "FROM sys_LogOperation " +
                       "GROUP BY YEAR(OperationTime); ";

            var cmd =
                DatabaseProvider.SystemDatabase.CreateCommand(sql1 + sql2);
            cmd.AddParam("OperationType1", "OpenForm");
            cmd.AddParam("OperationType2", "Exception");
            cmd.AddParam("OperationType3", "CustomError");

            var ds = DatabaseProvider.SystemDatabase.GetDataSet(cmd.Command);
            return ds;
        }

        public DataTable QueryLoginLog(string loginType, string loginUser,
            string loginIP, string loginMAC,
            DateTime LoginD1, DateTime LoginD2)
        {
            var cmd = _Database.CreateCommand("");
            var p = _Database.ParamSymboName; //SQL参数符号

            var sb = new StringBuilder();
            sb.AppendLine(
                "SELECT isid,Account,LoginResult,CurrentTime,IP,MAC,LoginType FROM sys_LogLogin WHERE 1=1 ");

            if (!string.IsNullOrEmpty(loginType))
            {
                sb.AppendLine($" AND LoginType={p}LoginType");
                cmd.AddParam("LoginType", loginType);
            }

            if (!string.IsNullOrEmpty(loginUser))
            {
                sb.AppendLine($" AND Account={p}Account");
                cmd.AddParam("Account", loginUser);
            }

            if (!string.IsNullOrEmpty(loginIP))
            {
                sb.AppendLine($" AND IP={p}IP");
                cmd.AddParam("IP", loginIP);
            }

            if (!string.IsNullOrEmpty(loginMAC))
            {
                sb.AppendLine($" AND MAC={p}MAC");
                cmd.AddParam("MAC", loginMAC);
            }

            if (LoginD1 > DateTime.Parse("1901-01-01"))
            {
                sb.AppendLine($" AND CurrentTime>={p}CurrentTime1");
                cmd.AddParam("CurrentTime1",
                    ConvertEx.ToCharYYYYMMDDLong(LoginD1));
            }

            if (LoginD2 > DateTime.Parse("1901-01-01"))
            {
                sb.AppendLine($" AND CurrentTime<={p}CurrentTime2");
                cmd.AddParam("CurrentTime2",
                    ConvertEx.ToCharYYYYMMDDLong(LoginD2));
            }

            cmd.Command.CommandText =
                CodeSafeHelper.GetSafeSQL(sb.ToStringEx());
            return _Database.GetTable(cmd.Command, "sys_LogLogin");
        }

        public DataTable QueryOperateLog(string Content, DateTime Date1,
            DateTime Date2, string OperateType,
            string User)
        {
            var cmd = _Database.CreateCommand("");
            var p = _Database.ParamSymboName; //SQL参数符号

            var sb = new StringBuilder();
            sb.AppendLine("SELECT * FROM sys_LogOperation WHERE 1=1 ");

            if (!string.IsNullOrEmpty(Content))
            {
                sb.AppendLine(
                    $" AND (MSG LIKE {p}MSG OR FormName LIKE {p}FormName OR FormCaption LIKE {p}FormCaption OR MenuName LIKE {p}MenuName)");
                cmd.AddParam("MSG", "%" + Content + "%");
                cmd.AddParam("FormName", "%" + Content + "%");
                cmd.AddParam("FormCaption", "%" + Content + "%");
                cmd.AddParam("MenuName", "%" + Content + "%");
            }

            if (!string.IsNullOrEmpty(OperateType))
            {
                sb.AppendLine($" AND OperationType={p}OperateType");
                cmd.AddParam("OperateType", OperateType);
            }

            if (!string.IsNullOrEmpty(User))
            {
                sb.AppendLine($" AND Account={p}Account");
                cmd.AddParam("Account", User);
            }

            if (Date1 > DateTime.Parse("1901-01-01"))
            {
                sb.AppendLine($" AND OperationTime>={p}OperationTime1");
                cmd.AddParam("OperationTime1", Date1);
            }

            if (Date2 > DateTime.Parse("1901-01-01"))
            {
                sb.AppendLine($" AND OperationTime<={p}OperationTime2");
                cmd.AddParam("OperationTime2", Date2);
            }

            cmd.Command.CommandText =
                CodeSafeHelper.GetSafeSQL(sb.ToStringEx());
            return _Database.GetTable(cmd.Command,
                sys_LogOperation.__TableName);
        }

        /// <summary>
        ///     写操作日志
        /// </summary>
        /// <param name="content">String或sys_LogOperation对象</param>
        public void WriteLog(object content)
        {
            if (content is sys_LogOperation)
            {
                var log = content as sys_LogOperation;

                var data = new DbDataUpdate(_Database);
                data.AddObject(log, "isid");
            }
            else if (content is string)
            {
                var log = new sys_LogOperation
                {
                    MSG = content.ToStringEx(),
                    OperationTime = DateTime.Now,
                    OperationType = LogTypeSystem.Normal.ToStringEx()
                };

                var data = new DbDataUpdate(_Database);
                data.AddObject(log, "isid");
            }
        }

        /// <summary>
        ///     高级查询 - 查询条件参数化
        /// </summary>
        /// <param name="whereSQL"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object AdvancedSearch(string whereSQL,
            DbParameterModel[] parameters)
        {
            var sql = "SELECT * FROM sys_LogOperation WHERE 1=1 ";

            var cmd = _Database.CreateCommand("");
            var p = _Database.ParamSymboName; //SQL参数符号

            //有查询条件
            if (!string.IsNullOrWhiteSpace(whereSQL))
            {
                //替换参数符号
                whereSQL = whereSQL.Replace(DbParameterModel.ParamSymbol, p);
                sql = sql + " AND " + whereSQL;
                if (parameters != null)
                    foreach (var m in parameters)
                        //替换参数符号
                        cmd.AddParam(
                            m.ParamName.Replace(DbParameterModel.ParamSymbol,
                                p), m.ParamValue);
            }

            //返回DataTable
            cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(sql);
            return _Database.GetTable(cmd.Command,
                sys_LogOperation.__TableName);
        }
    }
}