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

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

        public async Task<IEnumerable<CncSliceSate>> GetCncSliceSatesAsync(IEnumerable<int> machineIds, string startTime, string endTime, StatusCategory category = StatusCategory.Default)
        {
            var search = "WHERE 1=1 ";
            if (category == StatusCategory.HasChangeLine)
            {
                search += " AND Status <= 5 ";
            }
            else if (category == StatusCategory.HasGrinding)
            {
                search += " AND Status > 5 ";
            }
            else if (category == StatusCategory.Default)
            {
                search += " AND Status <= 4 ";
            }

            if (machineIds != null && machineIds.Any())
            {
                search += $" AND MachineId in ({string.Join(",", machineIds)})";
            }
            search += $" AND ((StartTime >= '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime >= '{startTime}' AND StartTime < '{endTime}' AND EndTime > '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{endTime}'))";

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $@"SELECT id, MachineID,StartTime,EndTime,Status,WordingModeId,WordingName,UserName,Remark FROM {CncSliceSate}
                                        {search}";

                    return await conn.QueryAsync<CncSliceSate>(executeQuery);
                }
            }
            catch
            {
                throw;
            }
        }

        public async Task<IEnumerable<CncSliceSate>> GetCncSliceSatesForAnalysisAsync(IEnumerable<int> machineIds, int errorType, string startTime, string endTime)
        {
            var search = "WHERE 1=1 AND EndTime > StartTime AND Status <= 4 ";
            search += $" AND ((StartTime >= '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime >= '{startTime}' AND StartTime < '{endTime}' AND EndTime > '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{endTime}'))";

            if (machineIds != null && machineIds.Any())
            {
                search += $" AND MachineId in ({string.Join(",", machineIds)})";
            }

            if (errorType != 0)
            {
                search += $" AND Status = {errorType}";
            }
            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $@"SELECT id, MachineID,StartTime,EndTime,Status,UserName,WordingModeId FROM {CncSliceSate}
                                        {search} ORDER BY MachineID, StartTime";

                    return await conn.QueryAsync<CncSliceSate>(executeQuery);
                }
            }
            catch
            {
                throw;
            }
        }

        public KeyValuePair<Pagination, IList<CncSliceSate>> GetCncSliceSatesForEditAsync(Pagination pagin, MachineSateCondition condition)
        {
            var search = "WHERE 1=1 AND EndTime > StartTime AND Status <= 4 ";

            if (condition.ErrType >= 0)
            {
                search += $" AND Status={condition.ErrType} ";
            }
            if (condition.BigSmall == 2 && !string.IsNullOrWhiteSpace(condition.BigSmallSpan)) //大于
            {
                search += $" AND (UNIX_TIMESTAMP(EndTime) - UNIX_TIMESTAMP(StartTime)) > {condition.BigSmallSpan} ";
            }
            if (condition.BigSmall == 1 && !string.IsNullOrWhiteSpace(condition.BigSmallSpan)) //小于
            {
                search += $" AND (UNIX_TIMESTAMP(EndTime) - UNIX_TIMESTAMP(StartTime)) < {condition.BigSmallSpan} ";
            }
            if (condition.StartTime != null && condition.EndTime != null)
            {
                //search += $" AND StartTime >= '{condition.StartTime}' AND EndTime <= '{condition.EndTime}'";
                search += $" AND ((StartTime >= '{condition.StartTime}' AND EndTime <= '{condition.EndTime}')";
                search += $" OR (StartTime < '{condition.StartTime}' AND EndTime > '{condition.EndTime}' AND EndTime <= '{condition.EndTime}')";
                search += $" OR (StartTime >= '{condition.StartTime}' AND StartTime < '{condition.EndTime}' AND EndTime > '{condition.EndTime}')";
                search += $" OR (StartTime < '{condition.StartTime}' AND EndTime > '{condition.EndTime}'))";
            }

            if (condition.MachinIds.Any())
            {
                var machineIds = string.Join(",", condition.MachinIds);
                search += $" AND MachineID in ({machineIds})";
            }
            var offset = (pagin.CurrentPageIndex - 1) * pagin.PageSize;
            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $@"SELECT id, MachineID,StartTime,EndTime,Status,WordingModeId,UserName,Remark,(UNIX_TIMESTAMP(EndTime) - UNIX_TIMESTAMP(StartTime)) Seconds FROM {CncSliceSate}
                                        {search} ORDER BY MachineID,StartTime LIMIT {offset},{pagin.PageSize}";
                    if (pagin.PageSize==0)
                    {
                        executeQuery = $@"SELECT id, MachineID,StartTime,EndTime,Status,WordingModeId,UserName,Remark,(UNIX_TIMESTAMP(EndTime) - UNIX_TIMESTAMP(StartTime)) Seconds FROM {CncSliceSate}
                                        {search} ORDER BY MachineID,StartTime";
                    }
                    var executeCount = $"SELECT COUNT(*) AS CountNum FROM {CncSliceSate} {search}";

                    var listScore = conn.Query<CncSliceSate>(executeQuery).ToList();
                    pagin.TotalItemCount = conn.Query<int>(executeCount).SingleOrDefault();

                    var result = new KeyValuePair<Pagination, IList<CncSliceSate>>(pagin, listScore);
                    return result;
                }
            } 
            catch
            {
                throw;
            }
        }

        public async Task<IEnumerable<CncSliceSate>> GetCncSliceSatesForEditAsync(MachineSateCondition condition)
        {
            var search = "WHERE 1=1 AND EndTime > StartTime AND Status <= 4 ";
            search += $" AND Status={condition.ErrType} ";
            if (condition.StartTime != null && condition.EndTime != null)
            {
                //search += $" AND StartTime >= '{condition.StartTime}' AND EndTime <= '{condition.EndTime}'";
                search += $" AND ((StartTime >= '{condition.StartTime}' AND EndTime <= '{condition.EndTime}')";
                search += $" OR (StartTime < '{condition.StartTime}' AND EndTime > '{condition.EndTime}' AND EndTime <= '{condition.EndTime}')";
                search += $" OR (StartTime >= '{condition.StartTime}' AND StartTime < '{condition.EndTime}' AND EndTime > '{condition.EndTime}')";
                search += $" OR (StartTime < '{condition.StartTime}' AND EndTime > '{condition.EndTime}'))";
            }

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

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $@"SELECT id, MachineID,StartTime,EndTime,Status,WordingModeId,UserName,Remark FROM {CncSliceSate}
                                        {search} ORDER BY MachineID,StartTime";

                    return await conn.QueryAsync<CncSliceSate>(executeQuery);
                }
            }
            catch
            {
                throw;
            }
        }

        public async Task<int> UpdateRemarkAsync(int id, int modeId, string userName, string remark, string startTime, string endTime)
        {
            try
            {
                var search = " AND Status <= 4 ";
                search += $" AND ((StartTime >= '{startTime}' AND EndTime <= '{endTime}')";
                search += $" OR (StartTime < '{startTime}' AND EndTime > '{startTime}' AND EndTime <= '{endTime}')";
                search += $" OR (StartTime >= '{startTime}' AND StartTime < '{endTime}' AND EndTime > '{endTime}')";
                search += $" OR (StartTime < '{startTime}' AND EndTime > '{endTime}'))";
                using (var conn = GetDbConnection())
                {
                    var getSateSql = $"SELECT id,MachineID,Status FROM {CncSliceSate} WHERE id={id}";
                    var sates = await conn.QueryAsync<CncSliceSate>(getSateSql);
                    var sate = sates.FirstOrDefault();
                    if (sate == null)
                    {
                        return 0;
                    }
                    var sql = $@"UPDATE {CncSliceSate}
                              SET WordingModeId={modeId},
                              UserName = '{userName}', Remark = '{remark}'
                              WHERE MachineID = {sate.MachineID} {search} ;";

                    return await conn.ExecuteAsync(sql);
                }
            }
            catch
            {
                throw;
            }
        }

        public async Task<int> UpdateMachineSliceWorkModeAsync(int machineId, int modeId, string modeName, string userName, string remark, string startTime, string endTime)
        {
            try
            {
                var search = " AND Status <= 4 ";
                search += $" AND ((StartTime >= '{startTime}' AND EndTime <= '{endTime}')";
                search += $" OR (StartTime < '{startTime}' AND EndTime > '{startTime}' AND EndTime <= '{endTime}')";
                search += $" OR (StartTime >= '{startTime}' AND StartTime < '{endTime}' AND EndTime > '{endTime}')";
                search += $" OR (StartTime < '{startTime}' AND EndTime > '{endTime}'))";
                using (var conn = GetDbConnection())
                {
                    var sql = $@"UPDATE {CncSliceSate}
                              SET WordingModeId={modeId},WordingName='{modeName}',
                              UserName = '{userName}', Remark = '{remark}'
                              WHERE MachineID = {machineId} 
                               {search};";

                    return await conn.ExecuteAsync(sql);
                }
            }
            catch
            {
                throw;
            }
        }

        public IEnumerable<CncSliceSate> GetCncSliceSates(IEnumerable<int> machineIds, string startTime, string endTime)
        {
            var search = "WHERE 1=1 AND EndTime > StartTime AND Status <= 4 ";
            
            search += $" AND ((StartTime >= '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime >= '{startTime}' AND StartTime < '{endTime}' AND EndTime > '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{endTime}'))";

            if (machineIds != null && machineIds.Any())
            {
                search += $" AND MachineId in ({string.Join(",", machineIds)})";
            }

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $@"SELECT id, MachineID,StartTime,EndTime,Status FROM {CncSliceSate}
                                        {search} ORDER BY MachineID, StartTime";

                    return conn.Query<CncSliceSate>(executeQuery);
                }
            }
            catch
            {
                throw;
            }
        }

        public async Task<IEnumerable<CncSliceSate>> GetCncSliceSatesForWorkingMode(IEnumerable<int> machineIds, IEnumerable<int> modeIds, string startTime, string endTime)
        {
            var search = "WHERE 1=1 AND EndTime > StartTime AND Status <= 4 ";

            search += $" AND ((StartTime >= '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{startTime}' AND EndTime <= '{endTime}')";
            search += $" OR (StartTime >= '{startTime}' AND StartTime < '{endTime}' AND EndTime > '{endTime}')";
            search += $" OR (StartTime < '{startTime}' AND EndTime > '{endTime}'))";

            if (machineIds != null && machineIds.Any())
            {
                search += $" AND MachineId in ({string.Join(",", machineIds)})";
            }

            if (modeIds != null && modeIds.Any())
            {
                search += $" AND WordingModeId in ({string.Join(",", modeIds)})";
            }

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $@"SELECT id, MachineID,StartTime,EndTime,Status,WordingModeId,WordingName,UpdateTime FROM {CncSliceSate}
                                        {search} ORDER BY MachineID, StartTime";

                    return await conn.QueryAsync<CncSliceSate>(executeQuery);
                }
            }
            catch
            {
                throw;
            }
        }

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