using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
//using OfficeOpenXml;
using System;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using WcfControlMonitorLib;


namespace WcfControlMonitorWebLib.Controllers
{
    [ApiController, Route("api/historyerrorquery")]
    public class HistoryErrorQueryController : ControllerBase
    {
        DataView dv = new DataView();

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="workArea">仓库编码</param>
        /// <param name="deviceIndex">设备索引</param>
        /// <param name="deviceErrorIndex">故障代码</param>
        /// <param name="startDateTimeFrom">发生时间 From</param>
        /// <param name="startDateTimeTo">发生时间 To</param>
        /// <param name="endDateTimeFrom">结束时间 From</param>
        /// <param name="endDateTimeTo">结束时间 To</param>
        /// <param name="sort"></param>
        /// <param name="offset"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [HttpGet]
        public IActionResult Get(
            [FromQuery] string workArea,
            [FromQuery] string deviceIndex,
            [FromQuery] string deviceErrorIndex,
            [FromQuery] string startDateTimeFrom,
            [FromQuery] string startDateTimeTo,
            [FromQuery] string endDateTimeFrom,
            [FromQuery] string endDateTimeTo,
            [FromQuery] string sort,
            [FromQuery] int? offset,
            [FromQuery] int? limit)
        {

            if (offset == null)
            {
                offset = 0;
            }
            if (limit == null)
            {
                limit = 30;
            }
            #region  排序信息需要转一下
            if (sort?.Length > 1)
            {
                var format = sort?.StartsWith("~") == true ? "{0} DESC" : "{0}";
                if ((bool)sort?.StartsWith("~"))
                {
                    //[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
                    //[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
                    //[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
                    switch (sort.Substring(2))
                    {
                        case "orkArea":
                            sort = string.Format(format, "仓库编码");
                            break;
                        case "escription":
                            sort = string.Format(format, "仓库名称");
                            break;
                        case "eviceIndex":
                            sort = string.Format(format, "设备索引");
                            break;
                        case "eviceName":
                            sort = string.Format(format, "设备名称");
                            break;
                        case "askNo":
                            sort = string.Format(format, "设备指令索引");
                            break;
                        case "rrorName":
                            sort = string.Format(format, "故障名称");
                            break;
                        case "rrorType":
                            sort = string.Format(format, "故障类型");
                            break;
                        case "rrorLevel":
                            sort = string.Format(format, "故障级别");
                            break;
                        case "tartDateTime":
                            sort = string.Format(format, "发生时间");
                            break;
                        case "ndDateTime":
                            sort = string.Format(format, "结束时间");
                            break;
                        default:
                            sort = "序号";
                            break;
                    }
                }
                else
                {
                    switch (sort.Substring(1))
                    {
                        case "orkArea":
                            sort = string.Format(format, "仓库编码");
                            break;
                        case "escription":
                            sort = string.Format(format, "仓库名称");
                            break;
                        case "eviceIndex":
                            sort = string.Format(format, "设备索引");
                            break;
                        case "eviceName":
                            sort = string.Format(format, "设备名称");
                            break;
                        case "askNo":
                            sort = string.Format(format, "设备指令索引");
                            break;
                        case "rrorName":
                            sort = string.Format(format, "故障名称");
                            break;
                        case "rrorType":
                            sort = string.Format(format, "故障类型");
                            break;
                        case "rrorLevel":
                            sort = string.Format(format, "故障级别");
                            break;
                        case "tartDateTime":
                            sort = string.Format(format, "发生时间");
                            break;
                        case "ndDateTime":
                            sort = string.Format(format, "结束时间");
                            break;
                        default:
                            sort = "序号";
                            break;
                    }
                }

            }
            else
            {
                sort = "序号";
            }
            #endregion
            #region  拼接 where 条件
            //[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
            //[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
            //[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
            var where = "1=1";
            if (!string.IsNullOrEmpty(workArea))
            {
                where = $"{where} AND 仓库编码 = {workArea}";
            }
            if (!string.IsNullOrEmpty(deviceIndex))
            {
                where = $"{where} AND 设备索引 = {deviceIndex}";
            }
            if (!string.IsNullOrEmpty(deviceErrorIndex))
            {
                where = $"{where} AND 故障代码 = {deviceErrorIndex}";
            }
            if (!string.IsNullOrEmpty(startDateTimeFrom))
            {
                where = $"{where} AND 发生时间 >= '{startDateTimeFrom}'";
            }
            if (!string.IsNullOrEmpty(startDateTimeTo))
            {
                where = $"{where} AND 发生时间 <= '{startDateTimeTo}'";
            }
            if (!string.IsNullOrEmpty(endDateTimeFrom))
            {
                where = $"{where} AND 结束时间 >= '{endDateTimeFrom}'";
            }
            if (!string.IsNullOrEmpty(endDateTimeTo))
            {
                where = $"{where} AND 结束时间 <= '{endDateTimeTo}'";
            }

            #endregion
            var sql = $"SELECT top {limit} [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM  V_Device_Error_log WHERE {where}) AS T WHERE ROW_NUM > {offset}";
            var sqlAll = $"SELECT [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM  V_Device_Error_log WHERE {where}) AS T WHERE ROW_NUM > 0";

            DataView dv1 = CStaticClass.dbo.ExceSQL(sqlAll.ToString()).Tables[0].DefaultView;//满足的条件的所有数据 返回总记录条数用

            dv = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;

            var items = new dynamic[dv.Count];
            for (int i = 0; i < dv.Count; i++)
            {
                items[i] = new
                {
                    //[序号] as ID,
                    //[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
                    //[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
                    //[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
                    id= Convert.ToInt32(dv[i]["ID"]),
                    workArea = Convert.ToInt32(dv[i]["WorkArea"]),
                    description = Convert.ToString(dv[i]["Description"]),
                    deviceIndex = Convert.ToInt32(dv[i]["DeviceIndex"]),
                    deviceName = Convert.ToString(dv[i]["DeviceName"]),
                    taskNo = Convert.ToString(dv[i]["TaskNo"]),
                    deviceErrorIndex = Convert.ToInt32(dv[i]["DeviceErrorIndex"]),
                    errorName = Convert.ToString(dv[i]["ErrorName"]),
                    errorType = Convert.ToString(dv[i]["ErrorType"]),
                    errorLevel = Convert.ToString(dv[i]["ErrorLevel"]),
                    startDateTime = Convert.ToString(dv[i]["StartDateTime"]),
                    endDateTime = Convert.ToString(dv[i]["EndDateTime"]),
                    errorTime = Convert.ToString(dv[i]["ErrorTime"]),
                };
            }
            return Ok(new { total = dv1.Count, items });
        }

       /// <summary>
       /// 导出excel 
       /// 暂时给关闭了 有些卡
       /// 可以根据条件导出 也可以全部导出 没考虑好
       /// </summary>
       /// <param name="sort"></param>
       /// <param name="offset"></param>
       /// <param name="limit"></param>
       /// <returns></returns>
        [HttpGet("excel")]
        [ProducesResponseType(200)]
        public async Task<IActionResult> Export(
            //[FromQuery] string workArea,
            //[FromQuery] string deviceIndex,
            //[FromQuery] string deviceErrorIndex,
            //[FromQuery] string startDateTimeFrom,
            //[FromQuery] string startDateTimeTo,
            //[FromQuery] string endDateTimeFrom,
            //[FromQuery] string endDateTimeTo,
            [FromQuery] string sort,
            [FromQuery] int? offset,
            [FromQuery] int? limit)
        {
            if (offset == null)
            {
                offset = 0;
            }
            if (limit == null)
            {
                limit = 30;
            }
            #region  排序信息需要转一下
            //if (sort?.Length > 1)
            //{
            //    var format = sort?.StartsWith("~") == true ? "{0} DESC" : "{0}";
            //    if ((bool)sort?.StartsWith("~"))
            //    {
            //        //[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
            //        //[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
            //        //[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
            //        switch (sort.Substring(2))
            //        {
            //            case "orkArea":
            //                sort = string.Format(format, "仓库编码");
            //                break;
            //            case "escription":
            //                sort = string.Format(format, "仓库名称");
            //                break;
            //            case "eviceIndex":
            //                sort = string.Format(format, "设备索引");
            //                break;
            //            case "eviceName":
            //                sort = string.Format(format, "设备名称");
            //                break;
            //            case "askNo":
            //                sort = string.Format(format, "设备指令索引");
            //                break;
            //            case "rrorName":
            //                sort = string.Format(format, "故障名称");
            //                break;
            //            case "rrorType":
            //                sort = string.Format(format, "故障类型");
            //                break;
            //            case "rrorLevel":
            //                sort = string.Format(format, "故障级别");
            //                break;
            //            case "tartDateTime":
            //                sort = string.Format(format, "发生时间");
            //                break;
            //            case "ndDateTime":
            //                sort = string.Format(format, "结束时间");
            //                break;
            //            default:
            //                sort = "序号";
            //                break;
            //        }
            //    }
            //    else
            //    {
            //        switch (sort.Substring(1))
            //        {
            //            case "orkArea":
            //                sort = string.Format(format, "仓库编码");
            //                break;
            //            case "escription":
            //                sort = string.Format(format, "仓库名称");
            //                break;
            //            case "eviceIndex":
            //                sort = string.Format(format, "设备索引");
            //                break;
            //            case "eviceName":
            //                sort = string.Format(format, "设备名称");
            //                break;
            //            case "askNo":
            //                sort = string.Format(format, "设备指令索引");
            //                break;
            //            case "rrorName":
            //                sort = string.Format(format, "故障名称");
            //                break;
            //            case "rrorType":
            //                sort = string.Format(format, "故障类型");
            //                break;
            //            case "rrorLevel":
            //                sort = string.Format(format, "故障级别");
            //                break;
            //            case "tartDateTime":
            //                sort = string.Format(format, "发生时间");
            //                break;
            //            case "ndDateTime":
            //                sort = string.Format(format, "结束时间");
            //                break;
            //            default:
            //                sort = "序号";
            //                break;
            //        }
            //    }

            //}
            //else
            //{
            //    sort = "序号";
            //}
            #endregion
            var where = "1=1";
            //if (!string.IsNullOrEmpty(workArea))
            //{
            //    where = $"{where} AND 仓库编码 = {workArea}";
            //}
            //if (!string.IsNullOrEmpty(deviceIndex))
            //{
            //    where = $"{where} AND 设备索引 = {deviceIndex}";
            //}
            //if (!string.IsNullOrEmpty(deviceErrorIndex))
            //{
            //    where = $"{where} AND 故障代码 = {deviceErrorIndex}";
            //}
            //if (!string.IsNullOrEmpty(startDateTimeFrom))
            //{
            //    where = $"{where} AND 发生时间 >= '{startDateTimeFrom}'";
            //}
            //if (!string.IsNullOrEmpty(startDateTimeTo))
            //{
            //    where = $"{where} AND 发生时间 <= '{startDateTimeTo}'";
            //}
            //if (!string.IsNullOrEmpty(endDateTimeFrom))
            //{
            //    where = $"{where} AND 结束时间 >= '{endDateTimeFrom}'";
            //}
            //if (!string.IsNullOrEmpty(endDateTimeTo))
            //{
            //    where = $"{where} AND 结束时间 <= '{endDateTimeTo}'";
            //}
            //var sql = $"SELECT top {limit} [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY {sort}) AS ROW_NUM FROM  V_Device_Error_log WHERE {where}) AS T WHERE ROW_NUM > {offset}";
            var sql = $"SELECT  [序号] as ID, [仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime FROM (SELECT [序号], [仓库编码] ,[仓库名称],[设备索引],[设备名称],[设备指令索引],[故障代码],[故障名称],[故障类型],[故障级别],[发生时间],[结束时间],[ 故障时间(分钟)],ROW_NUMBER() OVER (ORDER BY 序号) AS ROW_NUM FROM  V_Device_Error_log WHERE 1=1) AS T WHERE ROW_NUM > 0";
            dv = CStaticClass.dbo.ExceSQL(sql.ToString()).Tables[0].DefaultView;

            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("Sheet1");
            var column = 0;
            worksheet.Cells[1, ++column].Value = "序号";
            worksheet.Cells[1, ++column].Value = "仓库编码";
            worksheet.Cells[1, ++column].Value = "仓库名称";
            worksheet.Cells[1, ++column].Value = "设备索引";
            worksheet.Cells[1, ++column].Value = "设备名称";
            worksheet.Cells[1, ++column].Value = "设备指令索引";
            worksheet.Cells[1, ++column].Value = "故障代码";
            worksheet.Cells[1, ++column].Value = "故障名称";
            //worksheet.Cells[1, ++column].Value = "故障类型";
            //worksheet.Cells[1, ++column].Value = "故障级别";
            worksheet.Cells[1, ++column].Value = "发生时间";
            worksheet.Cells[1, ++column].Value = "结束时间";
            worksheet.Cells[1, ++column].Value = "故障时间(分钟)";

            for (int i = 0; i < dv.Count; i++)
            {
                column = 0;
                //[序号] as ID,
                //[仓库编码] AS WorkArea,[仓库名称] AS Description,[设备索引] AS DeviceIndex,[设备名称] AS DeviceName,
                //[设备指令索引] AS TaskNo,[故障代码] AS DeviceErrorIndex,[故障名称] AS ErrorName,[故障类型] AS ErrorType,
                //[故障级别]  AS ErrorLevel,[发生时间] AS StartDateTime,[结束时间] AS EndDateTime,[ 故障时间(分钟)] AS ErrorTime
                worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["ID"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["WorkArea"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["Description"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["DeviceIndex"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["DeviceName"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["TaskNo"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToInt32(dv[i]["DeviceErrorIndex"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorName"]);
                //worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorType"]);
                //worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorLevel"]);
                worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["StartDateTime"]);
                worksheet.Cells[i + 2, column].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
                worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["EndDateTime"]);
                worksheet.Cells[i + 2, column].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
                worksheet.Cells[i + 2, ++column].Value = Convert.ToString(dv[i]["ErrorTime"]);

            }
            return File(package.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }

        /// <summary>
        /// 批量处理
        /// </summary>
        /// <param name="data">ID 数组</param>
        /// <returns></returns>
        [HttpPost("batch")]
        public IActionResult Batch(
            [FromMember] string[] data)
        {
            int count = 0;
            // 写入数据库
            foreach (var item in data)
            {
                var sql = $"delete from T_Base_Device_Error_Log where id={item}";
                if (CStaticClass.dbo.ExecuteSql(sql.ToString()) > 0)
                {
                    count++;
                }
            }
            if (count > 0)
            {
                return StatusCode(200);
            }
            else
            {
                return StatusCode(422);
            }

        }
    }
}