NBDXSY/LeaRun.Application/LeaRun.Application.Service/JiangSuWaterResourceManage/AreaAndDeviceService.cs

using LeaRun.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LeaRun.Application.Entity.JiangSuWaterResource;
using System.Data;
using LeaRun.Util;
using LeaRun.Util.WebControl;
namespace LeaRun.Application.Service.JiangSuWaterResourceManage
{
    /// <summary>
    /// 区域 测点管理
    /// </summary>
    public class AreaAndDeviceService
    {
        SqlHelper sqlHelper = new SqlHelper("JSSYDB");

        #region 获取区域列表
        public IEnumerable<Area> GetAreaList()
        {
            try
            {
                string selSql = "SELECT * FROM VIEW_区域信息";
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                return DataHelper.DataTableToT<Area>(dtResult);
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

        #region 获取测点列表
        public DataTable GetMeterList()
        {
            try
            {
                string selSql = "SELECT* FROM dbo.设备信息 sb LEFT JOIN dbo.VIEW_区域信息 qy ON sb.管理ID = qy.id";
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                return dtResult;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 区域 分页
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="keyWord">查询关键字</param>
        /// <returns></returns>
        public IEnumerable<Area> GetAreaListByPage(Pagination pagination, string keyWord)
        {
            try
            {
                string selSql = "SELECT * FROM VIEW_区域信息 ";
                if (!string.IsNullOrEmpty(keyWord))
                {
                    selSql = selSql + " WHERE 名称 like '%" + keyWord + "%'";
                }

                int total = pagination.records;
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, "ID", true, pagination.rows, pagination.page, out total, null);
                pagination.records = total;

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

        #endregion

        #region 区域操作

        /// <summary>
        /// 区域增加
        /// </summary>
        /// <param name="area">区域实体</param>
        /// <param name="lgt">经度</param>
        /// <param name="lat">维度</param>
        /// <returns></returns>
        public bool SaveArea(Area area, string lgt, string lat)
        {
            try
            {
                //StringBuilder saveSql = new StringBuilder();
                string insertSql = DataHelper.ModelToInsertSql<Area>(area, "管理信息");
                string insertId = sqlHelper.ExecuteScalar(insertSql + " SELECT @@IDENTITY", CommandType.Text, null).ToString();
                //如果 经纬度不为空 则数据插入管理信息辅助表
                if (string.IsNullOrEmpty(lgt) && string.IsNullOrEmpty(lat))
                {
                    string insertLnt = "INSERT INTO 管理辅助信息(管理ID,名称,值) VALUES(" + insertId + ",'经度','" + lgt + "')";
                    sqlHelper.ExecuteNoParams(insertSql, CommandType.Text);
                    insertLnt = "INSERT INTO 管理辅助信息(管理ID,名称,值) VALUES(" + insertId + ",'纬度','" + lat + "')";
                    sqlHelper.ExecuteNoParams(insertSql, CommandType.Text);
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        #endregion

        /// <summary>
        /// 测点列表
        /// </summary>
        /// <param name="pagination">分页参数</param>
        /// <param name="managerId">管理ID</param>
        /// <param name="meterName">测点名称</param>
        /// <param name="yingshou">营收账号</param>
        /// <returns></returns>
        public DataTable GetGridMeterList(Pagination pagination, string managerId, string meterName, string yingshou)
        {
            try
            {
                string searchSql = "SELECT * FROM VIEW_设备详细信息 WHERE 协议类型='用户站'";
                if (!string.IsNullOrEmpty(managerId))
                {
                    searchSql = searchSql + " AND 管理ID = " + managerId;
                }
                if (!string.IsNullOrEmpty(meterName))
                {
                    searchSql = searchSql + " AND 名称 LIKE '%" + managerId + "%' ";
                }
                if (!string.IsNullOrEmpty(yingshou))
                {
                    searchSql = searchSql + " AND 营收账号 LIKE '%" + yingshou + "%' ";
                }

                int total = pagination.records;
                DataTable dtResult = sqlHelper.ExecuteDataTable(searchSql, "ID", true, pagination.rows, pagination.page, out total, null);
                pagination.records = total;
                return dtResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 测点列表-全部
        /// </summary>        
        /// <param name="managerId">管理ID</param>
        /// <param name="meterName">测点名称</param>
        /// <param name="yingshou">营收账号</param>
        /// <returns></returns>
        public DataTable GetGridMeterList(string managerId, string meterName, string yingshou)
        {
            try
            {
                string searchSql = "SELECT * FROM VIEW_设备详细信息 WHERE 协议类型='用户站'";
                if (!string.IsNullOrEmpty(managerId))
                {
                    searchSql = searchSql + " AND 管理ID = " + managerId;
                }
                if (!string.IsNullOrEmpty(meterName))
                {
                    searchSql = searchSql + " AND 名称 LIKE '%" + managerId + "%' ";
                }
                if (!string.IsNullOrEmpty(yingshou))
                {
                    searchSql = searchSql + " AND 营收账号 LIKE '%" + yingshou + "%' ";
                }

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

                return dtResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// type 0 （用户站 终端类型） 1 （通讯设备 设备协议） 2（传输设备 传输协议） 其他 全部查询
        /// </summary>
        /// <param name="types"></param>
        /// <returns></returns>
        public DataTable GetTerminalTypes(string types)
        {
            try
            {
                string selSql = "SELECT * FROM dbo.模板信息 ";
                switch (types)
                {
                    case "0":
                        selSql = selSql + " WHERE 协议类型='用户站'";
                        break;
                    case "1":
                        selSql = selSql + " WHERE 协议类型='通讯设备'";
                        break;
                    case "2":
                        selSql = selSql + " WHERE 协议类型='传输设备'";
                        break;
                }
                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);

                return dtResult;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// 获取数据通道数据
        /// </summary>
        /// <param name="chanleName">通道服务类型</param>
        /// <returns></returns>
        public DataTable GetDataChannele(string chanleName)
        {
            try
            {
                string selSql = "SELECT * FROM dbo.数据通道 ";
                if (string.IsNullOrEmpty(chanleName))
                {
                    selSql += " WHERE IN ('" + chanleName + "')";
                }

                DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
                return dtResult;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public bool SaveDataChannele(DataChannele entity)
        {
            try
            {
                string insertSql = DataHelper.ModelToInsertSql(entity, "数据通道");
                return sqlHelper.ExecuteNoQuery(insertSql, null) > 0 ? true : false;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        
    }
}