using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Siger.Middlelayer.Dapper.ResultData;
using Siger.Middlelayer.Dapper.SearchCondition;

namespace Siger.Middlelayer.Dapper
{
    public class AlarmAnalysResultRepository : DapperRepositoryBase
    {
        public AlarmAnalysResultRepository(int companyId, int projectId) : base(companyId, projectId)
        {
        }

        public IEnumerable<CncAlarmAnalysResult> GetCncAlarmAnalysResults(IEnumerable<int> machineIds)
        {
            var exceptions = new List<CncAlarmAnalysResult>();

            if (!machineIds.Any())
            {
                return exceptions;
            }
            var condition = $" AND source in ({string.Join(",", machineIds)})";
            using (var conn = GetDbConnection())
            {
                var noClosedSql = $@"SELECT id,code,source as machineId,Stime,`set` AS FaultType,level,message,Etime,status,IsFault
                                    FROM {CncAlarmAnalysresult} 
                                    WHERE status = 1 {condition} AND Etime > Stime ORDER BY machineId,Stime DESC";
                var result = conn.Query<CncAlarmAnalysResult>(noClosedSql);
                foreach (var analysResult in result)
                {
                    var executeQuery = $@"SELECT `code`,source as machineId, message, FaultContent,FaultReason,FaultSolution,Remark,IsFault from {CncAlarmAnalysresult} 
                                    WHERE source={analysResult.machineId} AND `code`={analysResult.code} AND FaultContent != '' LIMIT 0,1";
                    var except = conn.Query<CncAlarmAnalysResult>(executeQuery).FirstOrDefault();
                    if (except != null)
                    {
                        analysResult.FaultContent = except.FaultContent;
                        analysResult.FaultReason = except.FaultReason;
                        analysResult.FaultSolution = except.FaultSolution;
                        analysResult.Remark = except.Remark;
                    }
                    exceptions.Add(analysResult);
                }
            }

            return exceptions;
        }

        public async Task<IEnumerable<CncAlarmAnalysResult>> GetCncAlarmCount(IEnumerable<int> machineIds, string dtStart, string dtEnd)
        {
            var condition = $" AND source in ({string.Join(",", machineIds)})";
            using (var conn = GetDbConnection())
            {
                var noClosedSql = $@"SELECT id,code,source as machineId,Stime,`set` AS FaultType,level,message,Etime,status
                                    FROM {CncAlarmAnalysresult} 
                                    WHERE Stime>='{dtStart}' AND Etime<='{dtEnd}' AND Etime > Stime {condition}";
                //(Stime>='{dtStart}' AND Stime<='{dtEnd}' OR Etime>='{dtStart}' AND Etime<='{dtEnd}')
                var result = await conn.QueryAsync<CncAlarmAnalysResult>(noClosedSql);
                return result;
            }
        }

        public KeyValuePair<Pagination, IList<CncAlarmAnalysResult>> GetPagedAlarmAnalysResults(Pagination pagin, CncAlarmAnalysResultCondition condition)
        {
            if (!condition.MachinIds.Any())
            {
                return new KeyValuePair<Pagination, IList<CncAlarmAnalysResult>>(pagin, new List<CncAlarmAnalysResult>());
            }

            var search = "WHERE 1=1 AND Etime > Stime ";

            var machineIds = string.Join(",", condition.MachinIds);
            search += $" AND source in ({machineIds})";

            if (condition.STime != null && condition.ETime != null)
            {
                search += $" AND Stime >= '{condition.STime}' AND Etime <= '{condition.ETime}'";
            }

            if (!string.IsNullOrWhiteSpace(condition.AlarmCode))
            {
                search += $" AND code like '%{condition.AlarmCode}%'";
            }

            if (!string.IsNullOrWhiteSpace(condition.AlarmContent))
            {
                search += $" AND message like '%{condition.AlarmContent}%'";
            }

            

            var offset = (pagin.CurrentPageIndex - 1) * pagin.PageSize;
            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"SELECT id,code,source as machineId,Stime,`set` AS FaultType,level,message,Etime,status, IsFault
                                        FROM {CncAlarmAnalysresult}
                                        {search} ORDER BY Stime DESC LIMIT {offset},{pagin.PageSize}";

                var executeCount = $"SELECT COUNT(*) AS CountNum FROM {CncAlarmAnalysresult} {search}";

                var listScore = conn.Query<CncAlarmAnalysResult>(executeQuery).ToList();
                var exceptions = new List<CncAlarmAnalysResult>();
                foreach (var analysResult in listScore)
                {
                    executeQuery = $@"SELECT `code`,source as machineId, message, FaultContent,FaultReason,FaultSolution,Remark,IsFault from {CncAlarmAnalysresult} 
                                    WHERE source={analysResult.machineId} AND `code`={analysResult.code} AND FaultContent != '' LIMIT 0,1";
                    var except = conn.Query<CncAlarmAnalysResult>(executeQuery).FirstOrDefault();
                    if (except != null)
                    {
                        analysResult.FaultContent = except.FaultContent;
                        analysResult.FaultReason = except.FaultReason;
                        analysResult.FaultSolution = except.FaultSolution;
                        analysResult.Remark = except.Remark;
                        analysResult.IsFault = except.IsFault;
                    }
                    exceptions.Add(analysResult);
                }
                pagin.TotalItemCount = conn.Query<int>(executeCount).SingleOrDefault();

                var result = new KeyValuePair<Pagination, IList<CncAlarmAnalysResult>>(pagin, exceptions);
                return result;
            }
        }

        public KeyValuePair<Pagination, IList<CncAlarmAnalysResult>> GetPagedAlarmAnalysResultsForEdit(Pagination pagin, CncAlarmAnalysResultCondition condition)
        {
            var search = "WHERE 1=1 AND Etime > Stime ";

            if (!string.IsNullOrWhiteSpace(condition.AlarmCode))
            {
                search += $" AND code like '%{condition.AlarmCode}%'";
            }
            if (condition.MachinIds.Any())
            {
                var machineIds = string.Join(",", condition.MachinIds);
                search += $" AND source in ({machineIds})";
            }
            if (!string.IsNullOrWhiteSpace(condition.Status))
            {
                if (condition.Status == "1") //未标定
                {
                    search += " AND MarkTime is null ";
                }
                else if(condition.Status == "2") //已标定
                {
                    search += " AND MarkTime is not null ";
                }
            }
            var offset = (pagin.CurrentPageIndex - 1) * pagin.PageSize;
            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"SELECT `code`,source as machineId, message,IsFault, COUNT(1) as AlertTimes,MarkTime from {CncAlarmAnalysresult} 
                                     {search} GROUP BY `code`,source, message,MarkTime ,IsFault ORDER BY AlertTimes desc
                                     LIMIT {offset},{pagin.PageSize}";

                var executeCount = $@"SELECT COUNT(*) AS CountNum FROM (SELECT `code`,source as machineId, message,
                                        COUNT(1) as AlertTimes,MarkTime from {CncAlarmAnalysresult} { search}
                                        GROUP BY `code`,source, message,MarkTime) as tb";

                var listScore = conn.Query<CncAlarmAnalysResult>(executeQuery).ToList();

                var exceptions = new List<CncAlarmAnalysResult>();
                foreach (var analysResult in listScore)
                {
                    executeQuery = $@"SELECT id,`code`,source as machineId, message, FaultContent,FaultReason,FaultSolution,Remark,IsFault,MarkTime from {CncAlarmAnalysresult} 
                                    WHERE source={analysResult.machineId} AND `code`={analysResult.code} AND FaultContent != '' LIMIT 0,1";
                    var except = conn.Query<CncAlarmAnalysResult>(executeQuery).FirstOrDefault();
                    if (except != null)
                    {
                        analysResult.FaultContent = except.FaultContent;
                        analysResult.FaultReason = except.FaultReason;
                        analysResult.FaultSolution = except.FaultSolution;
                        analysResult.Remark = except.Remark;
                        analysResult.MarkTime = except.MarkTime;
                    }
                    exceptions.Add(analysResult);
                }
                pagin.TotalItemCount = conn.Query<int>(executeCount).SingleOrDefault();

                var result = new KeyValuePair<Pagination, IList<CncAlarmAnalysResult>>(pagin, exceptions);
                return result;
            }
        }

        public int UpdateFaults(int source, string code, string content, string reason, string solution, string remark, int isfault)
        {
            using (var conn = GetDbConnection())
            {
                var sql = $@"UPDATE {CncAlarmAnalysresult}
                              SET FaultContent = '{content}',
                                  FaultReason = '{reason}',FaultSolution = '{solution}',Remark = '{remark}', IsFault = {isfault}, MarkTime = '{DateTime.Now:yyyy-MM-dd HH:mm:ss}'
                                  WHERE `code` = {code} AND source={source};";
                return conn.Execute(sql);
            }
        }

        public IList<CncAlarmAnalysResult> GetNoPagedCncExceptionParamsSates(CncAlarmAnalysResultCondition condition)
        {
            var search = "WHERE 1=1 AND Etime > Stime AND IsFault = 1";
            if (condition.STime != null && condition.ETime != null)
            {
                search += $" AND Stime >= '{condition.STime}' AND Etime <= '{condition.ETime}'";
            }
            if (condition.MachinIds.Any())
            {
                var machineIds = string.Join(",", condition.MachinIds);
                search += $" AND source in ({machineIds})";
            }

            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"SELECT id,code,source as machineId,Stime,`set` AS FaultType,level,message,Etime,status,FaultContent,IsFault
                                         FROM {CncAlarmAnalysresult}
                                        {search} ORDER BY machineId,Stime DESC";

                return conn.Query<CncAlarmAnalysResult>(executeQuery).ToList();
            }
        }

        /// <summary>
        /// 设备报警代码
        /// </summary>
        /// <param name="machineId"></param>
        /// <returns></returns>
        public string MachineLastAlarmCode(int machineId)
        {
            using (var conn = GetDbConnection())
            {
                var noClosedSql = $@"SELECT code FROM {CncAlarmAnalysresult} 
                                    WHERE status = 1 AND source = {machineId} AND Etime > Stime ORDER BY Stime DESC limit 0,1";
                return conn.ExecuteScalar<string>(noClosedSql);
            }
        }

        public override void Dispose()
        {
            throw new NotImplementedException();
        }
    }
}
