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

namespace Siger.Middlelayer.Dapper
{
    public class ToolAlarmRepository:DapperRepositoryBase
    {
        public ToolAlarmRepository(int companyId, int projectId) : base(companyId, projectId)
        {
        }
        public IEnumerable<ToolAlarmStatis> GetToolAlarm(List<int> machineId, long startTime, long  endTime,int alarmGrade)
        {
            var Condition = MergerCondition(machineId);

            var search = "WHERE status!=0";
            search += $" AND alarm_time >= '{startTime}' AND alarm_time <= '{endTime}' AND machine_id in ({Condition})";
            if (alarmGrade != 0)
                search += $" and alarm_grade='{alarmGrade}'";
            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"SELECT id,machine_id as MachineID, spindle_name,cutter_location_name,program_no,alarm_grade,error_msg,alarm_time as alarmtime,used_life,status,tool_state FROM {KM_LocationAlarmTb} 
                                        {search} ORDER BY machine_id";

                return conn.Query<ToolAlarmStatis>(executeQuery);
            }
        }
        public KeyValuePair<Pagination, IList<ToolAlarmResult>> GetPagedToolAlarm(Pagination pagin, List<int> machineId, long startTime, long endTime,int alarmGrade)
        {
            var Condition = MergerCondition(machineId);
            var search = "WHERE status!=0";
            search += $" AND alarm_time >= '{startTime}' AND alarm_time <= '{endTime}' AND machine_id in ({Condition})";
            if (alarmGrade != 0)
                search += $" and alarm_grade='{alarmGrade}'";
            var offset = (pagin.CurrentPageIndex - 1) * pagin.PageSize;
            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"SELECT id,machine_id,spindle_name,cutter_location_name,program_no,alarm_time,alarm_time as alarmtime,error_msg,alarm_grade,used_life,record_type,tool_state FROM {KM_LocationAlarmTb} 
                                     {search} ORDER BY alarm_time DESC   LIMIT {offset},{pagin.PageSize}";

                var executeCount = $@"SELECT count(*) FROM {KM_LocationAlarmTb} 
                                     {search}";

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

                var result = new KeyValuePair<Pagination, IList<ToolAlarmResult>>(pagin, listScore.ToList());
                return result;
            }
        }
        public ToolAlarmResult GetToolAlarmObj(int id)
        {
            var search = $"WHERE id={id}";
            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"SELECT id,machine_id,spindle_name,cutter_location_name,program_no,alarm_time,alarm_time as alarmtime,error_msg,alarm_grade,used_life,status,tool_state FROM {KM_LocationAlarmTb} 
                                     {search} ORDER BY alarm_time DESC ";

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

                return listScore.FirstOrDefault();
            }
        }
        /// <summary>
        /// 根据报警时间 查找
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ToolAlarmResult GetToolAlarmObj(int machine,string spindle,string tool,string pn,long time)
        {
            var search = $"WHERE machine_id={machine} and spindle_name={spindle} and cutter_location_name={tool} and program_no='{pn}' and alarm_time={time}";
            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"SELECT id,machine_id,spindle_name,cutter_location_name,program_no,alarm_time,alarm_time as alarmtime,error_msg,alarm_grade,used_life,status,tool_state FROM {KM_LocationAlarmTb} 
                                     {search} ORDER BY alarm_time DESC ";

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

                return listScore.FirstOrDefault();
            }
        }
        /// <summary>
        /// 误报更新
        /// </summary>
        /// <param name="id"></param>
        /// <param name="tool_state"></param>
        /// <returns></returns>
        public bool UpdateToolAlarm(int id,int record_state,int tool_state)
        {
            var sql = $"UPDATE KM_LocationAlarm SET record_type={record_state} ,tool_state={tool_state} WHERE id={id}";
            using(var conn=GetDbConnection())
            {
               return conn.Execute(sql)>0;
            }

        }
        /// <summary>
        /// 添加漏报
        /// </summary>
        /// <param name="projectId"></param>
        /// <param name="machine"></param>
        /// <param name="spindle"></param>
        /// <param name="tool"></param>
        /// <param name="pn"></param>
        /// <param name="time"></param>
        /// <param name="msg"></param>
        /// <param name="state"></param>
        /// <param name="tool_state"></param>
        /// <returns></returns>
        public bool InsertToolAlarm(int projectId,int machine,string spindle,string tool,string pn,int time,string msg,int record_type, int tool_state)
        {
            var sql=$"INSERT INTO KM_LocationAlarm(project_id,machine_id,spindle_name,cutter_location_name,program_no,alarm_time,error_msg,record_type,tool_state)VALUES({projectId},{machine},{spindle},{tool},'{pn}',{time},'{msg}',{record_type},{tool_state})";
            using (var conn = GetDbConnection())
            {
                return conn.Execute(sql) > 0;
            }

        }
        public IEnumerable<ToolAlarmRecordData> AlarmLogFailurePhoto(int mid,int spindleId,int toolId,string program,int timeStamp)
        {

            var search = $@"WHERE machineID={mid} AND spindleID = '{spindleId}' AND  toolNum= '{toolId}' AND alarmTime='{timeStamp}' ORDER BY alarmTime DESC ";

            using (var conn = GetDbConnection())
            {
                var executeQuery = $@"select companyID,projectID,machineID,spindleID,programNum,toolNum,alarmTime,cycle_counter,alarmType,BLX_step_number,BLX_LL,BLX_UL,BLX_ult_LL,BLX_signal,Trend_time,Trend_feature,Trend_feature_out,Trend_feature_in,";
                 executeQuery+= $"Trend_feature_max,Trend_feature_max_out,Trend_feature_max_in,Trend_UL,Trend_max_UL,Trend_avg,Trend_max_avg,Trend_huandao from {KM_AlarmRecordData} {search}";

                return conn.Query<ToolAlarmRecordData>(executeQuery);
            }
        }

        public SlicePeriod GetSlicePeriod(string program,int machineId)
        {
            var search = $@"WHERE STATUS != 1 AND programNum = '{program}' AND machineID = {machineId} ORDER BY updatetime DESC LIMIT 1";

            using (var conn = GetDbConnection())
            {
                var executeQuery = $"SELECT starttime,endtime FROM {TMS_SlicePeriod} {search}";

                return conn.Query<SlicePeriod>(executeQuery).FirstOrDefault();
            }
        }
        private string MergerCondition(List<int>data)
        {
            string ret = string.Empty;
            for(int i=0;i<data.Count;i ++)
            {
                if (i!=data.Count-1)
                    ret = ret + "'" + data[i] + "'" + ",";
               else
                    ret = ret+"'"+ data[i]+"'";
            }
            return ret;
        }
        public override void Dispose()
        {

        }
    }
}
