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

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

        public IEnumerable<CncProduction> GetTimeRangeYileds(string startTime, string endTime, IEnumerable<int> machineIds)
        {
            var ids = string.Join(",", machineIds);
            var search = " WHERE status = 1 ";
            if (machineIds.Any() && !string.IsNullOrEmpty(ids))
            {
                search += $"AND MachineId in ({ ids})";
            }
            search += $" AND dataTime>='{startTime}' AND dataTime <='{endTime}'";

            using (var conn = GetDbConnection())
            {
                var sql = $"SELECT id, machineID, yield,programCode,dataTime,StartTime,EndTime FROM {CncProductionTb} {search}";
                return conn.Query<CncProduction>(sql);
            }
        }

        public IEnumerable<CncProduction> GetYileds(string startTime, string endTime, IEnumerable<int> machineIds)
        {
            var ids = string.Join(",", machineIds);
            var search = $" WHERE status = 1 ";
            if (machineIds.Any() && !string.IsNullOrEmpty(ids))
            {
                search += $"AND MachineId in ({ ids})";
            }
            search += $" AND dataTime>='{startTime}' AND dataTime <='{endTime}'";

            using (var conn = GetDbConnection())
            {
                var sql = $"SELECT id, machineID, yield,programCode,dataTime,CycleTime,comment FROM {CncProductionTb} {search}";
                return conn.Query<CncProduction>(sql);
            }
        }

        public async Task<IEnumerable<CncProduction>> GetYieldsAsync(string startTime, string endTime, IEnumerable<int> machineIds)
        {
            return await Task.Run(() => GetYileds(startTime, endTime, machineIds));
        }

        public IEnumerable<ProductYield> GetYieldByMachine(int machineId, string startTime, string endTime)
        {
            using (var conn = GetDbConnection())
            {
                var sql = $@"SELECT programCode, sum(yield) as yield FROM {CncProductionTb} WHERE status = 1 
                                    AND machineID = {machineId}
                                    AND dataTime BETWEEN '{startTime}' AND '{endTime}'
                                    GROUP BY programCode";
                return conn.Query<ProductYield>(sql, new
                {
                    Tb = CncProductionTb,
                    machineID = machineId,
                    startTime = startTime,
                    endTime = endTime
                });
            }
        }

        public int GetYieldsByMachine(int machineId, string startTime, string endTime)
        {
            using (var conn = GetDbConnection())
            {
                var sql = $@"SELECT sum(yield) as yield FROM {CncProductionTb} WHERE status = 1 
                                    AND machineID = {machineId}
                                    AND dataTime BETWEEN '{startTime}' AND '{endTime}'";
                return conn.ExecuteScalar<int>(sql);
            }
        }

        public async Task<int> GetYieldsByMachineAsync(int machineId, string startTime, string endTime)
        {
            return await Task.Run(() => GetYieldsByMachine(machineId, startTime, endTime));
        }

        /// <summary>
        /// 获取最后一个程序号
        /// </summary>
        /// <param name="machineId"></param>
        /// <returns></returns>
        public string GetProgramNo(int machineId)
        {
            using (var conn = GetDbConnection())
            {
                var sql = $@"SELECT programCode FROM {CncProductionTb} WHERE status = 1 
                                    AND machineID = {machineId} ORDER BY dataTime DESC limit 0,1";
                return conn.ExecuteScalar<string>(sql);
            }
        }

        public IEnumerable<string> GetLastProgramNos(int machineId, string startTime, string endTime)
        {
            using (var conn = GetDbConnection())
            {
                var sql = $@"SELECT programCode FROM {CncProductionTb} WHERE status = 1 
                                    AND machineID = {machineId}
                                    AND dataTime BETWEEN '{startTime}' AND '{endTime}' 
                                    GROUP BY programCode ORDER BY max(dataTime) desc limit 0,5";
                return conn.Query<string>(sql);
            }
        }

        public IEnumerable<CncProduction> GetYields(int id)
        {
            using (var conn = GetDbConnection())
            {
                var sql = $@"SELECT * FROM {CncProductionTb} WHERE status = 1 AND id > {id} AND comment IS NOT NULL AND comment != ''";
                return conn.Query<CncProduction>(sql);
            }
        }
        public IEnumerable<CncProduction> GetCncProductionTwo(int machineId, string startTime)
        {
            var search = $"WHERE status = 1  AND machineID = { machineId } AND StartTime >= '{startTime}'";

            try
            {
                using (var conn = GetDbConnection())
                {
                    var executeQuery = $@"SELECT id, machineID,StartTime,EndTime,yield,programCode FROM {CncProductionTb}
                                        {search}";

                    return conn.Query<CncProduction>(executeQuery);
                }
            }
            catch
            {
                throw;
            }
        }
        public override void Dispose()
        {

        }
    }
}
