NBDXSY/LeaRun.Application/LeaRun.Application.Service/SecondaryWaterSupply/DeviceService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LeaRun.Application.Entity.SecondaryWaterSupply;
using LeaRun.Application.IService.SecondaryWaterSupply;
using LeaRun.Data;
using System.Data;
using LeaRun.Util.WebControl;
using LeaRun.Util;

namespace LeaRun.Application.Service.SecondaryWaterSupply
{
    public class DeviceService : IDeviceService
    {
        SqlHelper sqlHelper = new SqlHelper("SecondyWaterSupplyDb");//二次洪水
        
        /// <summary>
        /// 保存设备信息
        /// </summary>
        /// <param name="device"></param>
        /// <returns></returns>
        public bool SaveDevice(DeviceEntity device) 
        {
            try
            {
                string insertSql = "INSERT INTO SecondaryWaterSupplyBase(PumpHouseName,ManufacturerCode,PumpingStationNumber,longitude,latitude)VALUES('"+device.PumpHouseName+"', '"+
                    device.ManufacturerCode+"', '"+device.PumpingStationNumber+"', '"+device.Longitude+"', '"+ device.Latitude+"')";

                int result = sqlHelper.ExecuteNoParams(insertSql, CommandType.Text);

                return result > 0;
            }
            catch (Exception ex) 
            {
                return false;            
            }
        }


        /// <summary>
        /// 获取设备列表
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="keyWord">查询关键字</param>
        /// <returns></returns>
        public IEnumerable<DeviceEntity> GetDeviceList(Pagination pagination , string keyWord)
        {
            try 
            {
                string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE DeleteMark=0";
                int total = pagination.records;
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, "SecondaryWaterSupplyId", true, pagination.rows, pagination.page, out total, null);
                pagination.records = total;

                return DataHelper.DataTableToT<DeviceEntity>(dtResult);
            }
            catch (Exception ex) 
            {
                return null;
            }
        }

        /// <summary>
        /// 获取所有设备列表
        /// </summary>      
        /// <returns></returns>
        public IEnumerable<DeviceEntity> GetAllDevice()
        {
            try
            {
                string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE DeleteMark=0";
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                return DataHelper.DataTableToT<DeviceEntity>(dtResult);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 更新设备信息
        /// </summary>
        /// <param name="device"></param>
        /// <returns></returns>
        public bool UpdateDevice(DeviceEntity device)
        {
            try
            {
                string updataSql = "UPDATE SecondaryWaterSupplyBase SET	PumpHouseName = '" + device.PumpHouseName + "', ManufacturerCode ='" + device.ManufacturerCode
                                    + "', PumpingStationNumber ='" + device.PumpingStationNumber + "', longitude ='" + device.Longitude + "', latitude ='"+ device.Latitude
                                    + "' WHERE SecondaryWaterSupplyId= " + device.SecondaryWaterSupplyId;

                int result = sqlHelper.ExecuteNoParams(updataSql, CommandType.Text);

                return result > 0;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 获取单一表设备信息
        /// </summary>
        /// <param name="SecondaryWaterSupplyId"></param>
        /// <returns></returns>
        public DeviceEntity GetDeviceById(string SecondaryWaterSupplyId)
        {
            try
            {
                string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE SecondaryWaterSupplyId= " + SecondaryWaterSupplyId;

                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);

                List<DeviceEntity> lists = DataHelper.DataTableToT<DeviceEntity>(dtResult);

                return lists[0];
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 删除设备信息
        /// </summary>
        /// <param name="SecondaryWaterSupplyId"></param>
        /// <returns></returns>
        public bool DeleteDevice(string SecondaryWaterSupplyId) 
        {
            try
            {
                string delSql = "UPDATE SecondaryWaterSupplyBase SET DeleteMark=1 WHERE SecondaryWaterSupplyId= " + SecondaryWaterSupplyId;

                int result = sqlHelper.ExecuteNoParams(delSql, CommandType.Text);

                return result > 0;

            }
            catch (Exception ex)
            {
                return false;
            }
            
        }

        /// <summary>
        /// 获取所有设备最新信息
        /// </summary>
        /// <returns></returns>
        public IEnumerable<DeviceEntity> GetAllDeviceData()
        {
            try
            {
                string selSql = "SELECT * FROM dbo.SecondaryWaterSupplyBase WHERE DeleteMark=0 ";
                DataTable dtDevice = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                StringBuilder hisSql = new StringBuilder();
                for (int row = 0; row < dtDevice.Rows.Count; row++)
                {
                    string tabName = dtDevice.Rows[row]["TableName"].ToString();
                    string deviceName = dtDevice.Rows[row]["PumpHouseName"].ToString();
                    hisSql.Append("SELECT TOP 1 '" + deviceName + "' as PumpHouseName , * FROM " + tabName + " ORDER BY GetDateTime DESC");
                    if (row < dtDevice.Rows.Count - 1)
                    {
                        hisSql.Append("UNION");
                    }
                }
                
                DataTable dtHis = sqlHelper.ExecuteDataTable(hisSql.ToString(),CommandType.Text, null);
                
                return DataHelper.DataTableToT<DeviceEntity>(dtHis);
            }
            catch(Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 获取设备实时数据
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<DeviceEntity> GetAllDeviceRealTimeData(string ids, Pagination pagination)
        {
            try
            {
                string selSql = "SELECT * FROM dbo.SecondaryWaterSupplyBase WHERE SecondaryWaterSupplyId IN (" + ids + ")";
                DataTable dtDevice = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                StringBuilder hisSql = new StringBuilder();
                for (int row = 0; row < dtDevice.Rows.Count; row++)
                {
                    string tabName = dtDevice.Rows[row]["TableName"].ToString();
                    string deviceName = dtDevice.Rows[row]["PumpHouseName"].ToString();
                    hisSql.Append("SELECT TOP 1 '" + deviceName + "' as PumpHouseName , * FROM " + tabName + " ORDER BY GetDateTime DESC");
                    if (row < dtDevice.Rows.Count - 1)
                    {
                        hisSql.Append("UNION");
                    }
                }
                int total = pagination.records;
                DataTable dtHis = sqlHelper.ExecuteDataTable(hisSql.ToString(), "SecondaryWaterSupplyId", true, pagination.rows, pagination.page, out total, null);
                pagination.records = total;

                return DataHelper.DataTableToT<DeviceEntity>(dtHis);
            }
            catch (Exception ex)
            {
                return null;
            }
        }
    }
}