using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Siger.Middlelayer.Common.Helpers;
using Siger.Middlelayer.Dapper.ResultData;
using Siger.Middlelayer.Common.Log;
using System.Runtime.Serialization;
using Siger.Middlelayer.Common;

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

        public IEnumerable<LocationYield> GetLocationYields(string channel, IEnumerable<int> machineIds, DateTime sdate, DateTime edate)
        {
            var start = UnixTimeHelper.GetUnixByShortDate(sdate.ToString());
            var end = UnixTimeHelper.GetUnixByShortDate(edate.ToString());

            var search = "WHERE 1=1";
            search += $" AND unix_timestamp(dataTime) >= '{start}' AND unix_timestamp(dataTime)<= '{end}' ";

            if (!string.IsNullOrWhiteSpace(channel))
            {
                search += $" AND sectionID = '{channel}' ";
            }
            if (machineIds != null && machineIds.Any())
            {
                search += $" AND machineID in ({string.Join(",", machineIds)})";
            }

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $"SELECT * FROM {LocationYieldTb} {search} ORDER BY machineID, dataTime";
                    Logger.WriteLineInfo("sql:" + executeQuery);
                    return conn.Query<LocationYield>(executeQuery);
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
                return new List<LocationYield>();
            }
        }
        /// <summary>
        /// 根据通道/产线ID  查询瓶颈工位产量
        /// </summary>
        /// <param name="machineIds">瓶颈工位 设备ID</param>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <returns></returns>
        public IEnumerable<LocationYield> GetLocationYields(IEnumerable<int> machineIds, DateTime sdate, DateTime edate)
        {
            //var start = UnixTimeHelper.GetUnixByShortDate(sdate.ToString());
            //var end = UnixTimeHelper.GetUnixByShortDate(edate.ToString());

            //var search = "WHERE 1=1";
            //search += $" AND unix_timestamp(dataTime) >= '{start}' AND unix_timestamp(dataTime)<= '{end}' ";
            //if (machineIds != null && machineIds.Any())
            //    search += $" AND machineID in ({string.Join(",", machineIds)})";
            //else
            //    return new List<LocationYield>();

            //try
            //{
            //    using (var conn = GetDbConnection())
            //    {
            //        var executeQuery = $"SELECT * FROM {LocationYieldTb} {search} ORDER BY machineID, dataTime";

            //        Logger.WriteLineInfo("sql:" + executeQuery);
            //        return conn.Query<LocationYield>(executeQuery);
            //    }
            //}
            //catch (Exception ex)
            //{
            //    Logger.WriteLineError(ex.Message);
            //    return new List<LocationYield>();
            //}

            var search = "WHERE 1=1";
            if (machineIds != null && machineIds.Any())
            {
                search += $" AND machineID in ({string.Join(",", machineIds)})";
            }
            var sql = $"SELECT * FROM { LocationYieldTb} {search} ORDER BY id limit 0,1";
            try
            {
                using (var conn = GetDbConnection())
                {
                    var yield = conn.QueryFirst<LocationYield>(sql);
                    if(yield != null)
                    {
                        search += $" AND dataTime >= '{yield.dataTime.Date.ToString(ParameterConstant.DateFormat)}' AND dataTime < '{yield.dataTime.AddDays(1).Date.ToString(ParameterConstant.DateFormat)}'";
                        sql = $"SELECT * FROM { LocationYieldTb} {search}  ORDER BY id limit 0,500";
                        return conn.Query<LocationYield>(sql);
                    }
                    else
                    {
                        return new List<LocationYield>();
                    }
                }
            }
            catch
            {
                return new List<LocationYield>();
            }
        }

        public IEnumerable<LocationYield> GetTopLocationYields()
        {
            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $"SELECT * FROM {LocationYieldTb} ORDER BY id desc Limit 0,10";
                    return conn.Query<LocationYield>(executeQuery);
                }
            }
            catch
            {
                return new List<LocationYield>();
            }
        }

        public IEnumerable<LocationYield> GetLastLocationYieldsData(IEnumerable<int> machineIds, DateTime edate)
        {
            var end = UnixTimeHelper.GetUnixByShortDate(edate.ToString());

            var search = "WHERE 1=1";
            search += $" AND unix_timestamp(startTime)< '{end}' ";
            if (machineIds != null && machineIds.Any())
                search += $" AND machineID in ({string.Join(",", machineIds)})";
            else
                return new List<LocationYield>();

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $"SELECT * FROM {LocationYieldTb} {search} ORDER BY endTime DESC LIMIT 2";

                    Logger.WriteLineInfo("sql:" + executeQuery);
                    return conn.Query<LocationYield>(executeQuery);
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
                return new List<LocationYield>();
            }
        }

        public IEnumerable<LocationYields> GetLocationYieldsBySectionIds(IEnumerable<int> sectionIds, DateTime sdate, DateTime edate)
        {
            var start = UnixTimeHelper.GetUnixByShortDate(sdate.ToString());
            var end = UnixTimeHelper.GetUnixByShortDate(edate.ToString());

            var search = "WHERE 1=1";
            search += $" AND unix_timestamp(dataTime) >= '{start}' AND unix_timestamp(dataTime)<= '{end}' ";
            if (sectionIds != null && sectionIds.Any())
                search += $" AND sectionID in ({string.Join(",", sectionIds)})";
            else
                return new List<LocationYields>();

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $"SELECT * FROM {LocationYieldTb} {search} ORDER BY machineID, dataTime";

                    Logger.WriteLineInfo("sql:" + executeQuery);
                    return conn.Query<LocationYields>(executeQuery);
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
                return new List<LocationYields>();
            }
        }
        /// <summary>
        /// 获取LocationYield信息
        /// </summary>
        /// <param name="machines"></param>
        /// <param name="type"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public async Task<PagedCollectionResult<LocationYield>> GetLocationYieldDataAsync(IEnumerable<int> machines, int type, int start, int end, int page, int pagesize)
        {
            var searchCondition = "WHERE 1=1";
            searchCondition += $" AND unix_timestamp(dataTime) >= '{start}' AND unix_timestamp(dataTime)<= '{end}' and subtraction > 0 ";
            if (machines != null && machines.Any())
            {
                searchCondition += $" AND machineID in ({string.Join(",", machines)})";
            }
            if (type != 0)
            {
                searchCondition += $" AND type = {type}";
            }
            try
            {
                using (var conn = GetDbConnection())
                {
                    var countQuery = $"SELECT count(*) FROM {LocationYieldTb} {searchCondition} ORDER BY machineID, dataTime ;";
                    var count = conn.ExecuteScalar<int>(countQuery);
                    var executeQuery = $"SELECT * FROM {LocationYieldTb} {searchCondition} ORDER BY machineID, dataTime LIMIT {pagesize} OFFSET {(page - 1) * pagesize};";
                    var entities = await conn.QueryAsync<LocationYield>(executeQuery);
                    return new PagedCollectionResult<LocationYield>(entities, count);

                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
                return new PagedCollectionResult<LocationYield>(new List<LocationYield>(), 0);
            }
        }
        /// <summary>
        /// 更新LocationYield中计划停机信息
        /// </summary>
        /// <param name="machine"></param>
        /// <param name="type"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public bool UpdateLocationYieldStopMachine(int machine, int type, int start, int end, string workOrderId = "")
        {
            var ret = false;
            try
            {
                using (var connetion = GetDbConnection())
                {
                    var sql = $"UPDATE `{LocationYieldTb}` SET  `type` = '{type}' , `workOrderId` = '{workOrderId}' WHERE unix_timestamp(endTime) >= '{start}' and unix_timestamp(endTime) <= '{end}' and machineID ={machine};";
                    var result = connetion.Execute(sql);
                    if (result > 0)
                    {
                        ret = true;
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
            }
            return ret;
        }

        /// <summary>
        /// 更新LocationYield中计划停机信息
        /// </summary>
        /// <param name="machine"></param>
        /// <param name="type"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="workOrderId"></param>
        /// <returns></returns>
        public async Task<bool> UpdateLocationYieldStopMachineAsync(int machine, int type, int start, int end, string workOrderId = "")
        {
            return await Task.Run(() => UpdateLocationYieldStopMachine(machine, type, start, end, workOrderId));
        }

        public bool UpdateYieldDataType(List<int> ids, int type, int mid, int time)
        {
            var searchCondition = " WHERE 1=1";
            if (ids == null || !ids.Any())
            {
                return false;
            }
            searchCondition += $" AND id in ({string.Join(",", ids)})";
            try
            {
                using (var conn = GetDbConnection())
                {
                    var sql = $"UPDATE {LocationYieldTb} SET type= {type} , editor = {mid} , edit_time = {time}{searchCondition};";
                    if (conn.Execute(sql) > 0)
                    {
                        return true;

                    }
                    return false;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
                return false;
            }
        }

        public async Task<double> GetOeeLoss(DateTime dtStart, DateTime dtEnd, IEnumerable<int> machines, IEnumerable<int> types, IEnumerable<string> workorders = null)
        {
            var ret = 0d;
            var start = UnixTimeHelper.ConvertDataTimeLong(dtStart);
            var end = UnixTimeHelper.ConvertDataTimeLong(dtEnd);
            var searchCondition = " WHERE 1=1";
            searchCondition += $" AND unix_timestamp(dataTime) >= '{start}' AND unix_timestamp(dataTime)<= '{end}' ";
            if (machines == null || !machines.Any())
            {
                return ret;
            }
            searchCondition += $" AND machineID in ({string.Join(",", machines)})";
            if (types != null)
            {
                types = types.Where(f => f != 0).ToList();
                if (types.Any())
                {
                    searchCondition += $" AND type in ({string.Join(",", types)})";
                }
            }
            if (workorders != null)
            {
                workorders = workorders.Where(f => !string.IsNullOrEmpty(f)).ToList();
                if (workorders.Any())
                {
                    searchCondition += $" AND workOrderId in ({string.Join(",", workorders)})";
                }
            }
            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $"SELECT SUM(subtraction) FROM {LocationYieldTb} {searchCondition}";

                    return await conn.ExecuteScalarAsync<double>(executeQuery);
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
                return 0d;
            }
        }
        public List<LocationYieldsType> GetOeeTypes(DateTime dtStart, DateTime dtEnd, IEnumerable<int> machines, IEnumerable<int> types)
        {
            var start = UnixTimeHelper.ConvertDataTimeLong(dtStart);
            var end = UnixTimeHelper.ConvertDataTimeLong(dtEnd);
            types = types.Where(f => f != 0).ToList();
            var ret = new List<LocationYieldsType>();
            var searchCondition = " WHERE 1=1";
            searchCondition += $" AND unix_timestamp(dataTime) >= '{start}' AND unix_timestamp(dataTime)<= '{end}' ";
            if (machines == null || !machines.Any())
            {
                return ret;
            }
            searchCondition += $" AND machineID in ({string.Join(",", machines)})";
            if (types.Any())
            {
                searchCondition += $" AND type in ({string.Join(",", types)})";
            }
            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $"SELECT DISTINCT type FROM {LocationYieldTb} {searchCondition}";
                    return conn.ExecuteScalar<List<LocationYieldsType>>(executeQuery);
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLineError(ex.Message);
                return new List<LocationYieldsType>();
            }
        }

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