qlhb/Ropin.Inspection.Api/TdevWebScadaController.cs

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Ropin.Inspection.Api.Common;
using Ropin.Inspection.Api.Controllers;
using Ropin.Inspection.Model;
using Ropin.Inspection.Service;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using InfluxData.Net.InfluxDb;
using InfluxData.Net.Common.Enums;
using Org.BouncyCastle.Utilities.Collections;
using Ropin.Inspection.Common.Helper;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Ropin.Inspection.Model.Common;
using InfluxData.Net.InfluxDb.Models.Responses;
using System.Data;
using System.Linq.Expressions;
using Ropin.Core.Common;
using Renci.SshNet;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Configuration;

namespace Ropin.Inspection.Api
{

    public class TdevWebScadaController : BaseController
    {
        public ILogger<TdevWebScadaController> _logger { get; }
        private readonly ITdevWebScadaService _TdevWebScadaService;
        private InfluxDbClient clientDb;
        private readonly IConfiguration _configuration;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="TdevWebScadaService"></param>
        /// <param name="logger"></param>
        public TdevWebScadaController(ITdevWebScadaService TdevWebScadaService, ILogger<TdevWebScadaController> logger, IConfiguration configuration)
        {
            _TdevWebScadaService = TdevWebScadaService;
            _logger = logger;
            _configuration = configuration;
        }

        /// <summary>
        /// 通过id获取云组态信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("GetWebScadaAsync/{id}")]
        [AllowAnonymous]
        public async Task<ApiResult> GetWebScadaAsync(string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new ApiResult(ReturnCode.GeneralError);
            }
            try
            {
                var content = await _TdevWebScadaService.GetConditionAsync(new TdevWebScadaSearchModel { C_ID = id });
                return new ApiResult<TdevWebScadaViewModel>(content.FirstOrDefault());
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
        }

        /// <summary>
        /// 获取所有云组态
        /// </summary>
        /// <returns></returns>
        [HttpGet("GetWebScadasAsync")]
        public async Task<ApiResult> GetWebScadasAsync()
        {
            try
            {
                var contentList = await _TdevWebScadaService.GetAllAsync();
                return new ApiResult<IEnumerable<TdevWebScadaViewModel>>(contentList);
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
        }

        /// <summary>
        /// 通过云组态名称条件查询
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        [HttpPost("GetWebScadasByAsync")]
        public async Task<ApiResult> GetWebScadasByAsync(TdevWebScadaSearchModel searchModel)
        {
            if (searchModel == null)
            {
                return new ApiResult(ReturnCode.ArgsError);
            }
            searchModel.IsPagination = false;
            try
            {
                var contentList = await _TdevWebScadaService.GetConditionAsync(searchModel);
                return new ApiResult<PagesModel<TdevWebScadaViewModel>>(new PagesModel<TdevWebScadaViewModel>(contentList, searchModel));
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
        }

        /// <summary>
        /// 创建云组态
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        [HttpPost("CreateWebScadaAsync")]
        public async Task<ApiResult> CreateWebScadaAsync(TdevWebScadaViewModel content)
        {
            if (content == null)
            {
                return new ApiResult(ReturnCode.ArgsError);
            }
            try
            {
                await _TdevWebScadaService.CreateOneAsync(content);
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
            return new ApiResult(ReturnCode.Success);
        }

        /// <summary>
        /// 删除云组态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpDelete("DeleteWebScadaAsync/{id}")]
        public async Task<ApiResult> DeleteWebScadaAsync(string id)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new ApiResult(ReturnCode.GeneralError);
            }
            try
            {
                await _TdevWebScadaService.DeleteAsync(id);
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
            return new ApiResult(ReturnCode.Success);
        }
        /// <summary>
        /// 更新云组态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="updateModel"></param>
        /// <returns></returns>
        [HttpPut("UpdateWebScadaAsync/{id}")]
        public async Task<ApiResult> UpdateWebScadaAsync(string id, TdevWebScadaUpdateModel updateModel)
        {
            if (string.IsNullOrEmpty(id))
            {
                return new ApiResult(ReturnCode.GeneralError);
            }
            try
            {
                await _TdevWebScadaService.UpdateAsync(id, updateModel);
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
            return new ApiResult(ReturnCode.Success);
        }
        /// <summary>
        /// 取设备点历史数据
        /// </summary>
        /// <returns></returns>
        [HttpGet("GetDevSpotSeries")]
        [AllowAnonymous]
        public async Task<object> GetDevSpotSeries()
        {
            try
            {
                HttpClient httpClient = new HttpClient();
                var msgData = new TpushMsgModel
                {
                };
                var httpRequestMessage = new HttpRequestMessage
                {
                    Method = HttpMethod.Get,
                    RequestUri = new Uri("http://www.dgt.net.cn:94/Device/GetDevSpotSeries"),
                    Content = new StringContent(JsonConvert.SerializeObject(msgData), Encoding.UTF8, "application/json")
                };
                var response = await httpClient.SendAsync(httpRequestMessage);
                string responseResult = await response.Content.ReadAsStringAsync();
                return responseResult;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 发送命令到设备
        /// </summary>
        /// <param name="boxNo"></param>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        [HttpPost("PublishDeviceCommandByAsync")]
        [AllowAnonymous]
        public async Task<object> GetDevicePointByAsync([FromQuery] string boxNo, [FromBody] DeviceCommandModel model)
        {
            try
            {
                HttpClient httpClient = new HttpClient();
                //var msgData = new TpushMsgModel
                //{
                //};
                var httpRequestMessage = new HttpRequestMessage
                {
                    Method = HttpMethod.Post,
                    RequestUri = new Uri("http://60.204.212.71:95/api/Value/PublishDeviceCommandByAsync?boxNo=" + boxNo),
                    Content = new StringContent(JsonConvert.SerializeObject(model), Encoding.UTF8, "application/json")
                };
                var response = await httpClient.SendAsync(httpRequestMessage);
                string responseResult = await response.Content.ReadAsStringAsync();
                return responseResult;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        /// <summary>
        /// 条件获取历史数据
        /// </summary>
        /// <param name="point"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        [HttpPost("GetDevSpotSeriesAsync")]
        [AllowAnonymous]
        public async Task<object> GetDevSpotSeriesAsync(DevSpotSeriesSearchModel searchModel)
        {
            if (searchModel == null || searchModel.point.Length == 0)
            {
                return new ApiResult(ReturnCode.ArgsError);
            }
            try
            {
                //var contentList = await _TdevWebScadaService.GetConditionAsync(searchModel);
                //return new ApiResult<PagesModel<TdevWebScadaViewModel>>(new PagesModel<TdevWebScadaViewModel>(contentList, searchModel));

                //string strStart = searchModel.start.ToString("yyyy-MM-ddTHH:mm:ssZ");
                //string strEnd = searchModel.end.ToString("yyyy-MM-ddTHH:mm:ssZ");

                string strStart = searchModel.start.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strEnd = searchModel.end.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strId = searchModel.point[0];
                string strIds = string.Join('|', searchModel.point);
                IniInflux();
                //传入查询命令，支持多条
                var queries = new[]
                {
                //"SELECT * FROM fanyidev WHERE time>'"+strStart+"'  and time< '"+strEnd + "' and Id = '"+strId+"' order by time desc TZ('Asia/Shanghai')"
                "SELECT * FROM fanyidev WHERE time>'"+strStart+"'  and time< '"+strEnd + "' and Id =~/^"+strIds+"$/ order by time desc TZ('Asia/Shanghai')"
                //"SELECT * FROM fanyidev WHERE time>'"+strStart+"'  and time< '"+strEnd + "' and Id = '"+strId+"' order by time desc"
            };
                var dbName = "fanyidb";
                //从指定库中查询数据
                var response = await clientDb.Client.QueryAsync(queries, dbName);
                if (!response.Any()) {
                    var data = new
                    {
                        tabName=new string[0],
                        data = new PagesModel<object>(null, searchModel)
                    };
                    return new ApiResult<object>(data);
                }
                //得到Serie集合对象（返回执行多个查询的结果）
                List<Serie> series = response.ToList();
                //取出第一条命令的查询结果，是一个集合
                var list = series[0].Values;
                var serie1s = list.Select(x => new Serie1
                {
                    Time = x[0].ToString(),
                    ID = x[1].ToString(),
                    Value = Convert.ToDecimal(x[2]),
                    Name = x[3].ToString()
                }).ToList();
               //var tabName1 = serie1s.GroupBy(x => x.Name).ToList();
                var tabName1 = serie1s.GroupBy(x => new { x.ID,x.Name}).ToList();

                //var dataTable = serie1s.ToPivotTable(x => x.Name, x => x.Time, x => x.Any() ? x.Average(s => s.Value) : 0);
                var dataTable = serie1s.ToPivotTable(x => x.Name, x => x.Time, x => x.Any() ? x.Select(s => s.Value).First() : 0);

                string[] tabName = new string[tabName1.Count + 1];
                IList<IList<object>> tabVlue = new List<IList<object>>();

                tabName[0] = "记录时间";
                int num = 1;
                //foreach (var item in tabName1)
                //{
                //    tabName[num] = item.Key;
                //    num++;
                //}
                foreach (var item in tabName1)
                {
                    int index = tabName1.FindIndex(t => t.Key.ID == searchModel.point[num - 1]);
                    string vals = tabName1.Find(t => t.Key.ID == searchModel.point[num - 1]).Key.Name;
                    tabName[num] = vals;
                    num++;
                }
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    string[] objects=new string[tabName.Length];
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        //var vals = dataTable.Rows[i][j].ToString();
                        //objects[j]= vals;
                        if (j>0)
                        {
                            int index = tabName1.FindIndex(t => t.Key.ID == searchModel.point[j-1]);
                            var vals1 = dataTable.Rows[i][index+1].ToString();
                            objects[j] = vals1;
                        }
                        else
                        {
                            var vals = dataTable.Rows[i][j].ToString();
                            objects[j] = vals;
                        }
                    }
                    tabVlue.Add(objects);
                }
                //从集合中取出第一条数据
                //var info_model = list.FirstOrDefault();
                //return list;
                if (tabVlue.Any())
                {
                    searchModel.TotalCount = tabVlue.First() != null ? tabVlue.ToList().Count : 0;
                    PagesModel<object> pages = new PagesModel<object>(searchModel.IsPagination ? tabVlue.Skip((searchModel.PageIndex - 1) * searchModel.PageSize).Take(searchModel.PageSize) : tabVlue, searchModel);
                    var data = new
                    {
                        tabName,
                        data= pages
                    };
                    return new ApiResult<object>(data);
                }
                else
                {
                    var data = new
                    {
                        tabName = new string[0],
                        data = new PagesModel<object>(null, searchModel)
                    };
                    return new ApiResult<object>(data);
                }
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }

        }

        private void IniInflux()
        {
            //连接InfluxDb的API地址、账号、密码

            var infuxUrl = _configuration["IniInflux:infuxUrl"]?.ToString(); //"http://60.204.212.71:8085/";

            var infuxUser = _configuration["IniInflux:infuxUser"]?.ToString(); //"admin";

            var infuxPwd = _configuration["IniInflux:infuxPwd"]?.ToString(); // "123456";

            //创建InfluxDbClient实例

            clientDb = new InfluxDbClient(infuxUrl, infuxUser, infuxPwd, InfluxDbVersion.Latest);

        }

        /// <summary>
        /// 发送邮件(附件为历史记录)-原方法
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        [HttpPost("SendSpotSeriesToEmail0")]
        [AllowAnonymous]
        public async Task<ApiResult> SendRecordsToEmail0(SendSpotSeriesEmailModel searchModel)
        {
            if (searchModel == null)
            {
                return new ApiResult(ReturnCode.ArgsError);
            }

            try
            {
                string strStart = searchModel.start.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strEnd = searchModel.end.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strId = searchModel.point[0];
                string strIds = string.Join('|', searchModel.point);

                IniInflux();
                //传入查询命令，支持多条
                var queries = new[]
                {
                "SELECT * FROM fanyidev WHERE time>'"+strStart+"'  and time< '"+strEnd + "' and Id =~/^"+strIds+"$/ order by time desc TZ('Asia/Shanghai')"
            };
                var dbName = "fanyidb";
                //从指定库中查询数据
                var response = await clientDb.Client.QueryAsync(queries, dbName);
                if (!response.Any())
                {
                    string msg = searchModel.start + "到" + searchModel.end + "日期之间没有查找到数据！";
                    return new ApiResult(ReturnCode.DataError, msg);
                }
                //得到Serie集合对象（返回执行多个查询的结果）
                var series = response.ToList();
                //取出第一条命令的查询结果，是一个集合
                var list = series[0].Values;

                var serie1s = list.Select(x => new Serie1
                {
                    Time = x[0].ToString(),
                    ID = x[1].ToString(),
                    Value = Convert.ToDecimal(x[2]),
                    Name = x[3].ToString()
                }).ToList();

                var dataTable = serie1s.ToPivotTable(x => x.Name, x => x.Time, x => x.Any() ? x.Sum(s => s.Value) : 0);

                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("sheet1");
                var headRow = sheet.CreateRow(0);
                headRow.CreateCell(0).SetCellValue("日期");
                for (int i = 1; i < dataTable.Columns.Count; i++)
                {
                    headRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);
                }

                //第二行,列名
                IFont font1 = workbook.CreateFont();
                font1.IsBold = true;
                font1.FontHeightInPoints = 12;
                font1.FontName = "宋体";
                ICellStyle headCellStyle = workbook.CreateCellStyle();
                headCellStyle.SetFont(font1);
                //边框
                headCellStyle.BorderBottom = BorderStyle.Thin;
                headCellStyle.BorderLeft = BorderStyle.Thin;
                headCellStyle.BorderRight = BorderStyle.Thin;
                headCellStyle.BorderTop = BorderStyle.Thin;
                foreach (var item in headRow.Cells)
                {
                    item.CellStyle = headCellStyle;
                }

                int start = 1;
                IFont font3 = workbook.CreateFont();
                font3.FontHeightInPoints = 9;
                font3.FontName = "宋体";
                ICellStyle contentCellStyle = workbook.CreateCellStyle();
                contentCellStyle.SetFont(font3);
                //边框
                contentCellStyle.BorderBottom = BorderStyle.Thin;
                contentCellStyle.BorderLeft = BorderStyle.Thin;
                contentCellStyle.BorderRight = BorderStyle.Thin;
                contentCellStyle.BorderTop = BorderStyle.Thin;

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    var row = sheet.CreateRow(start);
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        if (j==0)
                        {
                            row.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());
                        }
                        else
                        {
                            row.CreateCell(j).SetCellValue(Convert.ToDouble(dataTable.Rows[i][j]));
                        }
                    }

                    start++;
                    foreach (var cell in row.Cells)
                    {
                        cell.CellStyle = contentCellStyle;
                    }
                }

                //foreach (var item in list)
                //{
                //    var row = sheet.CreateRow(start);
                //    row.CreateCell(0).SetCellValue(item[0].ToString());
                //    row.CreateCell(1).SetCellValue(Convert.ToDouble(item[2]));
                //    start++;
                //    foreach (var cell in row.Cells)
                //    {
                //        cell.CellStyle = contentCellStyle;
                //    }
                //}
                // 自适应单元格
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    sheet.AutoSizeRow(i);
                }
                for (int i = 0; i < 7; i++)
                {
                    sheet.AutoSizeColumn(i, true);
                }
                using (var stream = new NpoiMemoryStream())
                {
                    workbook.Write(stream);
                    stream.Seek(0, SeekOrigin.Begin);
                    await Task.Run(() =>
                    {
                        EmailHelper.SendEmail(searchModel.Mails, $"{searchModel.DevName}设备点历史记录{searchModel.start}-{searchModel.end}", "", "报表见附件", $"{searchModel.DevName}设备点历史记录{searchModel.start}-{searchModel.end}报表.xlsx", "application/vnd.ms-excel", stream);
                    });
                }

            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
            return new ApiResult(ReturnCode.Success);
        }

        /// <summary>
        /// 发送邮件(附件为历史记录)-速度优化
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        [HttpPost("SendSpotSeriesToEmail")]
        [AllowAnonymous]
        public async Task<ApiResult> SendRecordsToEmail(SendSpotSeriesEmailModel searchModel)
        {
            if (searchModel == null)
            {
                return new ApiResult(ReturnCode.ArgsError);
            }
            try
            {
                string strStart = searchModel.start.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strEnd = searchModel.end.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strId = searchModel.point[0];
                string strIds = string.Join('|', searchModel.point);

                IniInflux();
                //传入查询命令，支持多条
                var queries = new[]
                {
                "SELECT * FROM fanyidev WHERE time>'"+strStart+"'  and time< '"+strEnd + "' and Id =~/^"+strIds+"$/ order by time desc TZ('Asia/Shanghai')"
            };
                var dbName = "fanyidb";
                //从指定库中查询数据
                var response = await clientDb.Client.QueryAsync(queries, dbName);
                if (!response.Any())
                {
                    string msg = searchModel.start + "到" + searchModel.end + "日期之间没有查找到数据！";
                    return new ApiResult(ReturnCode.DataError, msg);
                }
                //得到Serie集合对象（返回执行多个查询的结果）
                var series = response.ToList();
                //取出第一条命令的查询结果，是一个集合
                var list = series[0].Values;

                var serie1s = list.Select(x => new Serie1
                {
                    Time = x[0].ToString(),
                    ID = x[1].ToString(),
                    Value = Convert.ToDecimal(x[2]),
                    Name = x[3].ToString()
                }).ToList();
                var tableColumns = serie1s.Select(x => x.Name).Distinct().ToList();
                var tabName1 = serie1s.GroupBy(x => new { x.ID, x.Name }).ToList();
                var rows = serie1s.GroupBy(item => item.Time).
                    Select(rowGroup => new {
                        keys = rowGroup.Key,
                        values = tableColumns.GroupJoin(
                            rowGroup,
                            p => p,
                            n => n.Name,
                            (p, t) => t.Select(item => item.Value).First()
                            )
                    });
                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("sheet1");
                var headRow = sheet.CreateRow(0);
                headRow.CreateCell(0).SetCellValue("日期");
                for (int i = 1; i < tableColumns.Count + 1; i++)
                {
                    int index = tabName1.FindIndex(t => t.Key.ID == searchModel.point[i - 1]);
                    string vals = tabName1.Find(t => t.Key.ID == searchModel.point[i - 1]).Key.Name;
                    headRow.CreateCell(i).SetCellValue(vals.ToString());
                    //headRow.CreateCell(i).SetCellValue(tableColumns[i - 1].ToString());
                }
                #region 样式
                //第二行,列名
                IFont font1 = workbook.CreateFont();
                font1.IsBold = true;
                font1.FontHeightInPoints = 12;
                font1.FontName = "宋体";
                ICellStyle headCellStyle = workbook.CreateCellStyle();
                headCellStyle.SetFont(font1);
                //边框
                headCellStyle.BorderBottom = BorderStyle.Thin;
                headCellStyle.BorderLeft = BorderStyle.Thin;
                headCellStyle.BorderRight = BorderStyle.Thin;
                headCellStyle.BorderTop = BorderStyle.Thin;
                foreach (var item in headRow.Cells)
                {
                    item.CellStyle = headCellStyle;
                }

                int start = 1;
                IFont font3 = workbook.CreateFont();
                font3.FontHeightInPoints = 9;
                font3.FontName = "宋体";
                ICellStyle contentCellStyle = workbook.CreateCellStyle();
                contentCellStyle.SetFont(font3);
                //边框
                contentCellStyle.BorderBottom = BorderStyle.Thin;
                contentCellStyle.BorderLeft = BorderStyle.Thin;
                contentCellStyle.BorderRight = BorderStyle.Thin;
                contentCellStyle.BorderTop = BorderStyle.Thin;
                #endregion

                foreach (var item in rows)
                {
                    var items = item.values.Cast<object>().ToList();
                    var row = sheet.CreateRow(start);
                    row.CreateCell(0).SetCellValue(item.keys);
                    int j = 1;
                    foreach (var ite in items)
                    {
                        //string vals= EnumerableHelper.GetValues(ite.ToString());
                        //row.CreateCell(j).SetCellValue(Convert.ToDouble(ite));
                        int index = tabName1.FindIndex(t => t.Key.ID == searchModel.point[j-1]);
                        double vals = Convert.ToDouble(items[index]);                      
                        row.CreateCell(j).SetCellValue(vals);
                        j++;
                    }

                    start++;
                    foreach (var cell in row.Cells)
                    {
                        cell.CellStyle = contentCellStyle;
                    }
                }
                // 自适应单元格
                for (int i = 0; i < sheet.LastRowNum; i++)
                {
                    sheet.AutoSizeRow(i);
                }
                for (int i = 0; i < 7; i++)
                {
                    sheet.AutoSizeColumn(i, true);
                }
                string emailName = $"{searchModel.DevName}设备点历史记录{searchModel.start.ToString("yyyy-MM-dd HH:mm:ss")}至{searchModel.end.ToString("yyyy-MM-dd HH:mm:ss")}";
                using (var stream = new NpoiMemoryStream()) {
                    workbook.Write(stream);
                    stream.Seek(0, SeekOrigin.Begin);
                    await Task.Run(() =>
                    {
                        EmailHelper.SendEmail(searchModel.Mails, emailName, "", "报表见附件", $"{emailName}.xlsx", "application/vnd.ms-excel", stream);
                    });
                } 

            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }
            return new ApiResult(ReturnCode.Success);
        }


        /// <summary>
        /// 条件获取历史数据-折线图
        /// </summary>
        /// <param name="searchModel"></param>
        /// <returns></returns>
        [HttpPost("GetDevSpotSeriesLineChart")]
        [AllowAnonymous]
        public async Task<object> GetDevSpotSeriesLineChart(DevSpotSeriesSearchModel searchModel)
        {
            if (searchModel == null || searchModel.point.Length == 0)
            {
                return new ApiResult(ReturnCode.ArgsError);
            }
            try
            {
                string strStart = searchModel.start.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strEnd = searchModel.end.AddHours(-8).ToString("yyyy-MM-ddTHH:mm:ssZ");
                string strId = searchModel.point[0];
                string strIds = string.Join('|', searchModel.point);
                IniInflux();
                //传入查询命令，支持多条
                var queries = new[]
                {
                "SELECT * FROM fanyidev WHERE time>'"+strStart+"'  and time< '"+strEnd + "' and Id =~/^"+strIds+"$/ order by time desc TZ('Asia/Shanghai')"
            };
                var dbName = "fanyidb";
                //从指定库中查询数据
                var response = await clientDb.Client.QueryAsync(queries, dbName);
                if (!response.Any())
                {
                    return new ApiResult<object>(new
                    {
                        x = new List<string>(),
                        y = new List<YEntity>()
                    });
                }
                //得到Serie集合对象（返回执行多个查询的结果）
                List<Serie> series = response.ToList();
                //取出第一条命令的查询结果，是一个集合
                var list = series[0].Values;
                var serie1s = list.Select(x => new Serie1
                {
                    Time = x[0].ToString(),
                    ID = x[1].ToString(),
                    Value = Convert.ToDecimal(x[2]),
                    Name = x[3].ToString()
                }).ToList();
                var tabName1 = serie1s.GroupBy(x => x.Name).ToList();
                var dateArry = (serie1s.OrderBy(x=>x.Time)).GroupBy(x => x.Time).ToList();

                List<string> tabName =new List<string>();
                foreach (var item in tabName1)
                {
                    tabName.Add(item.Key);
                }
                List<string> x = new List<string>();
                foreach (var item in dateArry)
                {
                    x.Add(item.Key);
                }
                List<YEntity> yEntities = new List<YEntity>();
                foreach (var item in x)
                {
                    var ll= serie1s.Where(t => t.Time == item).ToList();
                    if (yEntities.Count==0)
                    {
                        foreach (var index in tabName)
                        {
                            YEntity yEntity = new YEntity();
                            yEntity.data = new List<decimal>();
                            var aa = (ll==null?null:(ll.Find(t => t.Name == index)));
                            yEntity.name = index;
                            if (aa!=null)
                            {
                                yEntity.data.Add(aa.Value);
                            }
                            else
                            {
                                yEntity.data.Add(0);
                            }
                            yEntities.Add(yEntity);
                        }
                    }
                    else
                    {
                        foreach (var index in tabName)
                        { 
                            var yData= yEntities.Find(y => y.name == index);
                            if (yData != null)
                            {
                                var aa = (ll == null ? null : (ll.Find(t => t.Name == index)));
                                if (aa != null)
                                {
                                    yData.data.Add(aa.Value);
                                }
                                else
                                {
                                    yData.data.Add(0);
                                }
                            }
                            else
                            {
                                YEntity yEntity = new YEntity();
                                yEntity.data = new List<decimal>();
                                var aa = (ll == null ? null : (ll.Find(t => t.Name == index)));
                                yEntity.name = index;
                                if (aa != null)
                                {
                                    yEntity.data.Add(aa.Value);
                                }
                                else
                                {
                                    yEntity.data.Add(0);
                                }
                                yEntities.Add(yEntity);
                            }
                        }
                    }
                }
                var data = new
                {
                    x,
                    y=yEntities,
                };
                return new ApiResult<object>(data);
            }
            catch (Exception ex)
            {
                return new ApiResult(ReturnCode.GeneralError, ex.Message);
            }

        }
    }

    public class Serie1
    {
        public string Time { get; set; }
        public string Name { get; set; }
        public decimal Value { get; set; }
        public string ID { get; set; }
    }
    public class YEntity
    {
        public string name { get; set; }
        public List<decimal> data { get; set; }
    }
}